Add autonumber to query

  • Thread starter Thread starter Paul S
  • Start date Start date
P

Paul S

Can you create and autonumber field in a query - ie. a
field that will consecutively number the resulting rows of
a query? And how?
 
Paul said:
Can you create and autonumber field in a query - ie. a
field that will consecutively number the resulting rows of
a query? And how?


To do that you have to have a field that can be used to
uiquely order the records. If you have that, then you can
use a subquery to calculate the position of each record in
the query:

SELECT table.*,
(SELECT Count(*)
FROM table AS X
WHERE X.sortfield <= table.sortfield
) AS Rank
FROM table
 
-----Original Message-----



To do that you have to have a field that can be used to
uiquely order the records. If you have that, then you can
use a subquery to calculate the position of each record in
the query:

SELECT table.*,
(SELECT Count(*)
FROM table AS X
WHERE X.sortfield <= table.sortfield
) AS Rank
FROM table
If you have two sort fields? simply do an 'AND' ?
 
-----Original Message-----
If you have two sort fields? simply do an 'AND' ?


Not quite a simple AND, but, with two sort fields, it's not
too bad:

SELECT table.*,
(SELECT Count(*) + 1
FROM table AS X
WHERE (X.sortfield1 < table.sortfield1)
OR (X.sortfield1 = table.sortfield1
AND X.sortfield2 < table.sortfield2)
) AS Rank
FROM table
 
Back
Top