Add autonumber to query

[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?

 

[Marshall Barton]

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

 

[Guest]

-----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' ?

 

[Marshall Barton]

-----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