Select sequential row number in query?

  • Thread starter Thread starter Steve
  • Start date Start date
S

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
 
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.
 
Back
Top