Hi NG,

I have a query where I get routes through a city, like this

ROUTE              ||    Passengers   |||
A-B-C-D                   144
A-B-E-X                    72
B-F-G-H-J                 66
B-F-K-L-X                  12
B-C-D-F-G                  7
G-H-X-A                     1

The result is a GROUP BY ROUTE and a count. What I want to add now is a
column SYNONYM where each Route has a Number, eg.

ROUTE              ||    Passengers   |||   SYNONYM
A-B-C-D                   144                    1
A-B-E-X                    72                     2
B-F-G-H-J                 66                     3
B-F-K-L-X                  12                     4
B-C-D-F-G                  7                      5
G-H-X-A                     1                       6

Is that easy? YES, but how can I do it?

Thank you very much

Rudi

Dump the results into a temp table with an identity column and select
the rows back from the temp table

------------------------------------------------------------------------------------------
"I sense many useless updates in you... Useless updates lead to
fragmentation...  Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/

See the MS support article ( support.microsoft.com ) : 186133

Also SQL Server 2005 has new ranking functions ( RANK, ROW_NUMBER etc. )
which makes solutions for such requirements simpler.

Thank you very much,

but there is one thing I have not mentioned: what if I group by several
columns, eg

ROUTE              ||    Wekkday |||   SYNONYM
A-B-C-D                   1                    1
A-B-C-D                   2                    1
B-F-G-H-J                 1                    2
B-F-G-H-J                 3                    2
B-F-G-H-J                 4                    2
G-H-X-A                    1                    3

I want to have 1 Synonym for every Route. Is this possible?

Thank you for your help

Rudi

Sure, if you look at the query Tom posted, just change the correlation like:

SELECT t1.route, t1.Weekday ,
       ( SELECT COUNT( DISTINCT t2.ROUTE )
           FROM tbl t2
          WHERE t2.ROUTE <= t1.ROUTE )
  FROM tbl t1 ;

Sounds like a running count:

select
    *
,    synonym = (select count (*) from MyTable i
                        where i.ROUTE <= o.ROUTE)
from
    MyTable    o
order by
    ROUTE

I have a query where I get routes through a city, like this

ROUTE              ||    Passengers   |||
A-B-C-D                   144
A-B-E-X                    72
B-F-G-H-J                 66
B-F-K-L-X                  12
B-C-D-F-G                  7
G-H-X-A                     1

The result is a GROUP BY ROUTE and a count. What I want to add now is a
column SYNONYM where each Route has a Number, eg.

ROUTE              ||    Passengers   |||   SYNONYM
A-B-C-D                   144                    1
A-B-E-X                    72                     2
B-F-G-H-J                 66                     3
B-F-K-L-X                  12                     4
B-C-D-F-G                  7                      5
G-H-X-A                     1                       6

Is that easy? YES, but how can I do it?

Thank you very much

Rudi

DB Design issue
Help selecting maximum number of characters
How to calculate comparisons of fields between records
Deleting large amount of data on sql server 2000
Order problem