Adding a Row Number in Query

问题:

I have a table in my database in Access 2013.

Table : city

ID_city city
1       Tetuan
5       Rabat
9       Marrakech
10      Agadir
15      Laayoun

I wish to add the Rowid number beside them:

Rowid   ID_city city
1       1       Tetuan
2       5       Rabat
3       9       Marrakech
4       10      Agadir
5       15      Laayoun

回答1:

One way to do this is to use the count function in a subquery. Not sure it scales well though and there are probably better ways...

select 
    (select count(*) from city where ID_city <= t1.ID_city) as row_number,
    *
from city t1

回答2:

The best way to do it is by using a self-join...

    SELECT
    COUNT(*) AS Rowid,
    C.ID_City,
    C.city
    FROM City C
    INNER JOIN City C1 ON C.ID_City >= C1.ID_City
    GROUP By C.ID_City, C.city

回答3:

A little VBA goes a long way...

'Module level variables; values will persist between function calls
'To reset the row number, you have to explicitly call Reset
Dim lastValue As Integer

Public Function RowNumber(x) As Integer
    'We need this parameter so Access will call the function on each row, instead of only once
    lastValue = lastValue +1
    RowNumber = lastValue
End Function

Public Sub Reset()
    lastValue = 0
End Sub

SQL statement:

SELECT RowNumber(city) AS RowID, *
FROM City

回答4:

another option: (http://www.openwinforms.com/row_number_to_sql_select.html)

  SELECT ROW_NUMBER() 
  OVER (ORDER BY ID_city) AS Rowid, 
  ID_City, city
  FROM city