How to select row numbers?

  • Thread starter Thread starter Jarek
  • Start date Start date
J

Jarek

I have to display a row number left to the row, e.g. like this:

Select ???, Name, City from Customers

Nr. Name City

1 Smith London
2 Wang Shanghai
3 Honda Tokyo
4 Vronov Moscow
5 Kowalski Warsaw
6 Müller Berlin

The number should always begin with 1 and continue, no matter how I sort or
select the records.

What to put instead of those "???" in my above SQL statement?
 
Dear Jarek:

Not a simple subject. It is far easier to do this in a report than a
query.

For your suggested data, you could do it this way when the data is
sorted by Name:

SELECT (SELECT COUNT(*) + 1 FROM YourTable T1
WHERE T1.Name < T.Name) AS Nr,
Name, City
FROM YourTable T
ORDER BY Name

However, when 2 people have the same Name, they will have the same Nr.
Even though the query can list them only one at a time, rows with the
same Name are actually "on top of one another" and not really ordered
as you see them. As such, this technique is called Ranking, not
numbering the rows.

Another query could be built for the table ordered by City with
similar results.

Rows in a table or in a query do not intrinsically have any order
except for the order you impose on them. If your sorting is not
unique, then rows with the same sort keys will actually not be in any
order within the subset of those with the same key values, even though
they are displayed one at a time. Ranking will follow this same rule,
only it will be much more apparent.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top