Select Row number

[Kent Chen]

Hi, in SQL 6.5, is anybody know how to add row number as a column use SELETE
statement.

i.e.: I don't have the Row column in the table, but I need write one line
of "select " to have following results:

Row Name Desc
------ --------- -----------
1 xxxx xxxx
2 xxxxx xxxxx
3 xxxxx xxxxx

thanks in advance!

kent

[Charles Bretana]

Kent,

This is known as a "Ranking" problem, and has several solutions depending on
the type of data in the table, and how you want the records ordered
(sorted). The key is whetehr or not the fields or fields that you want the
records ordered by is(are) unique (i.e., whether there exixts multiple
records with the same value for that column(s).

The solutions to the problem are easiest in the case where the Sorting rule
is by a unique column, in that case there is a smple subquery solution based
on the idea of counting, for each record, the number of records which
values of the ordering column equal to or greater than the value for the
specified record.... This gives the "rank" and effectively numbers the
records....

Assuming that you want the records ordered by a single column, (PKCol) which
is unique........

Select <Fields>,
(Select Count(*) From TableName IT
Where IT.PKCol <= OT.PKCol) As Rank
From TableName OT
Order By OT.PKCol

There is another similar method involving a Self-Join.

If the ordering column is not unique, all sorts of complexities arise, which
are solvable, (using temporary tables and/or cursors), but for those - if
they're applicable, I direct you to the Brainteasers chapter of Ron Soukup
and Kelen Delaney's excellent book,

"Inside SQL Sever 7.0" MS Press....

Regards,

Charly

Kent Chen <ch...@home.com> wrote in message
news:#VRVuB8A$GA.160@cppssbbsa03...