Select sequential row number in query?

[Steve]

Hi,

Is it possible to have a select query which not only
selects data from a table, but also returns a sequential
number, starting at 1, for each row?

Thanks,
Steve

 

[Sandra Daigle]

You can use a subquery to do this - here's a simple example which includes a
rownumber for a list of customers which are sorted by CustName and using
CustName as the ranking field. Hopefully you can adapt this to your
situation. Note that the subquery can only return one field, in this case
it's a count. Also note that this is not going to be very efficient if your
query returns more than a couple of thousand rows (maybe less if the rest of
the query is very complex).

SELECT
(select
count(*)
from
customers as c2
where
c2.custname < c1.custname) +1 as RowNumber,
c1.Custname,
c1.customerid
FROM
Customers as c1
ORDER BY
by c1.custname;

Another way to do this can be found at http://www.lebans.com/rownumber.htm.