Query that can number records on the fly???

  • Thread starter Thread starter Michael Kintner
  • Start date Start date
M

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
 
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?
 
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
 
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);
 
Back
Top