[sql] Row numbers in query result using Microsoft Access

I always use this query in sql server to get Row number in a table:

SELECT *
FROM   (SELECT *,
               Row_number()
                 OVER(
                   ORDER BY [myidentitycolumn]) RowID
        FROM   mytable) sub
WHERE  rowid = 15  

Now I am working in Access 2010 and this seems to be not working. Is there any replacement for this query in Access?

I needed the best x results of points per team.

Ranking does not solves this problem when there are results with equal points. So I need a recordnumber

I made a VBA function in Access to create a recordnumber that resets on ID change.

You have to query this query with where recordnumber <= x to get the points per team.

NB Access changes the record-number

1. when you query the query filtered on record number
2. when you filter out some results
3. when you change the sort order

That is not what I thought that would happen.

Solved this by using a temporary table and saving the recordnumbers and keys or an extra field in the table.

SELECT ID, Points, RecordNumberOffId([ID}) AS Recordnumber
FROM Team ORDER BY ID ASC, Points DESC;

It uses 3 module level variables to remember between calls

Dim PreviousID As Long
Dim PreviousRecordNumber As Long
Dim TimeLastID As Date

Public Function RecordNumberOffID(ID As Long) As Long 
'ID is sortgroup identity
'Reset if last call longer dan nn seconds in the past
If Time() - TimeLastID > 0.0003 Then '0,000277778 = 1 second
    PreviousID = 0
    PreviousRecordNumber = 0
End If
If ID <> PreviousID Then
    PreviousRecordNumber = 0
    PreviousID = ID
End If
PreviousRecordNumber = PreviousRecordNumber + 1
RecordNumberOffID = PreviousRecordNumber
TimeLastID = Time()
End Function

Though this is an old question, this has worked for me, but I've never tested its efficiency...

SELECT 
    (SELECT COUNT(t1.SourceID) 
     FROM [SourceTable] t1 
     WHERE t1.SourceID<t2.SourceID) AS RowID, 
    t2.field2, 
    t2.field3, 
    t2.field4, 
    t2.field5
FROM 
    SourceTable AS t2
ORDER BY 
    t2.SourceID;

Some advantages of this method:

• It doesn't rely on the order of the table, either - the RowID is calculated on its actual value and those that are less than it.
• This method can be applied to any (primary key) type (e.g. Number, String or Date).
• This method is fairly SQL agnostic, or requires very little adaptation.

Final Thoughts

Though this will work with practically any data type, I must emphasise that, for some, it may create other problems. For instance, with strings, consider:

ID     Description    ROWID
aaa    Aardvark           1
bbb    Bear               2
ccc    Canary             3

If I were to insert: bba Boar, then the Canary RowID will change...

ID     Description    ROWID
aaa    Aardvark           1
bbb    Bear               2
bba    Boar               3
ccc    Canary             4

Since I am sorting alphabetically on a string field and NOT by ID, the Count(*) and DCOUNT() approaches didn't work for me. My solution was to write a function that returns the Row Number:

Option Compare Database
Option Explicit
Private Rst As Recordset

Public Function GetRowNum(ID As Long) As Long
  If Rst Is Nothing Then
    Set Rst = CurrentDb.OpenRecordset("SELECT ID FROM FileList ORDER BY RealName")
  End If
  Rst.FindFirst "ID=" & ID
  GetRowNum = Rst.AbsolutePosition + 1
' Release the Rst 1 sec after it's last use
'------------------------------------------
  SetTimer Application.hWndAccessApp, 1, 1000, AddressOf ReleaseRst  
End Function


Private Sub ReleaseRst(ByVal hWnd As LongPtr, ByVal uMsg As Long, ByVal nIDEEvent As Long, ByVal dwTime As Long)
  KillTimer Application.hWndAccessApp, 1 
  Set Rst = Nothing
End Sub

Thanks for your solutions above! DCount did the trick for me too!

I had to use a combination of date columns and a unique identifier for the sorting portion of it (as well as some additional conditions), so here is what I ended up doing: 1) I had to check if DateColumnA was null, then check if DateColumnB was null, then use DateColumnC; then, if multiple records have the same date value, they all end up with the same id! 2) So, I figured I would use the integer unique ID of the table, and add it up to the time as "minutes". This will always provide different results 3) Finally, the logic above results in the count starting in 0... so just add 1!

SELECT 
 1+DCount("[RequestID]","[Request]","Archived=0 and ProjectPhase <> 2 and iif(isnull(DateColumnA)=true,iif(isnull(DateColumnB)=true,DateColumnC,DateColumnB),DateColumnA)+(RequestID/3600) < #" & if(isnull(DateColumnA)=true,iif(isnull(DateColumnB)=true,DateColumnC,DateColumnB),DateColumnA) + (RequestID/3600) & "#") AS RowID, 
FROM 
 Request
ORDER BY 1

I hope this helps you out!

Another way to assign a row number in a query is to use the DCount function.

SELECT *, DCount("[ID]","[mytable]","[ID]<=" & [ID]) AS row_id
FROM [mytable]
WHERE row_id=15

I might be late. Simply add a new field ID in the table with type AutoNumber. This will generate unique IDs and can utilize in Access too