Get row number in query

[Boon]

Hello,

I have one table with one column that lists all customers' name.

e.g.

NAME
Brian
Dan
John
..
..
..



I want to create a select query that shows two columns. First column is
dummy ID and second column is name.

e.g.
ID NAME
Cus001 Brian
Cus002 Dan
Cus003 John
..
..
..


How can I do this? I am thinking that in column ID I can use something like

ID:"Cus" & getrow() ... But it doesn't work.

thanks
Boon

 

[Michel Walsh]

Using MS SQL Server 2005 or later?


SELECT Row_Number( ) OVER( ORDER BY name) AS rowNumber, name FROM tableName

which works even if there are duplicated name

Using Jet? ASSUMING, this time, there is no duplicate name, you can use:


SELECT a.name, COUNT(*) AS rank
FROM tableName As a INNER JOIN tableName As b
ON a.name >= b.name
GROUP BY a.name


Note that the row number, or the rank, are numerical and you need to
concatenate the right string to the right format to get what you described
(even if that can be done outside the query itself, which, for MS SQL
Server, will reduce the amount of transferred data).



Vanderghast, Access MVP.