Query that can number records on the fly???

[Michael Kintner]

How can I make a query number each line in sequence of records found?
Example if a query return 6 lines of records, I would like the query to
return 1 for record 1, 2 for record 2 and so on.

Example:
1 Red House
2 Blue House
3 Orange House
4 Green House
5 Yellow House

 

[Ken Snell \(MVP\)]

You'd need to include a "ranking" field to give you that number. What is the
SQL statement of the current query? How is the "number sequence" to be
defined?

 

[Michael Kintner]

the current sql is select color from houses

I would like to query to number each house as displayed in order from the
query.

Something like: select Count+1 as SeqNumb, color from tables.

I know this does not work but only as an example of how it might work.
(smile)

Mike

 

[Ken Snell \(MVP\)]

So there is no specific "order" that you need, you just want each record to
have a number?

This is a generic way of doing this:

SELECT Houses.Color,
(SELECT Count(T.*) FROM Houses AS T
WHERE T.Color <= Houses.Color)
AS.ColorRank
ORDER BY
(SELECT Count(T.*) FROM Houses AS T
WHERE T.Color <= Houses.Color);