(Ms Access) Row_Number() Over Partition

Question

How can I convert the row_number() function with an over partition on MS ACCESS? What I want to achieve is:

from this table:

ID  | EntryDate  
10  | 2016-10-10
10  | 2016-12-10
10  | 2016-12-31
10  | 2017-01-31
10  | 2017-03-31
11  | 2015-01-31
11  | 2017-01-31

To this output, showing only the top 3 latest of each ID:

ID  | EntryDate  
10  | 2016-12-31
10  | 2017-01-31
10  | 2017-03-31
11  | 2015-01-31
11  | 2017-01-31

On SQL Server, i can achieved this using the following code:

select T.[ID],
   T.[AptEndDate],
from (
 select T.[ID],
        T.[AptEndDate],
        row_number() over(partition by T.[ID] order by T.[AptEndDate] desc) as rn
 from Table1 as T
 ) as T
where T.rn <= 3;

score 5 · Answer 1 · answered Mar 07 '17 at 03:18

Consider a count correlated subquery which can work in any RDBMS.

select T.[ID], T.[EntryDate]
from 
 (select sub.[ID],
         sub.[EntryDate],
         (select count(*) from Table1 c
          where c.ID = sub.ID 
          and c.[EntryDate] >= sub.[EntryDate]) as rn
 from Table1 as sub
 ) as T
where T.rn <= 3;

score 1 · Answer 2 · answered Mar 07 '17 at 07:57

It might be simpler and faster to use Top n - as you mention yourself:

Select T.[ID], T.[EntryDate]
From Table1 As T
Where T.[EntryDate] In
    (Select Top 3 S.[EntryDate]
    From Table1 As S
    Where S.[ID] = T.[ID]
    Order By S.[EntryDate] Desc)
Order By T.[ID] Asc, T.[EntryDate] Asc

Joel Coehoorn · Answer 3 · 2017-03-07T02:38:02.443

0

Anything using the OVER clause is something known as a Windowing Function. Unfortunately, MS Access does not have support for Windowing Functions.The easiest solution in this case may be to back to VBA code :(

edited Mar 07 '17 at 02:38

answered Mar 07 '17 at 02:32

Joel Coehoorn

399,467
113
570
794

do you have any idea how i can implement it using VBA code? – user-DY Mar 07 '17 at 02:44

score 0 · Answer 4 · answered Nov 12 '19 at 14:39

Public Const tableName As String = "[TransactionalData$]"
Public Const parentId As String = "parentId"
Public Const elementId As String = "Id"
Public Const informationalField As String = "Label"
Sub TransactionalQuery(Optional ByVal Id As Integer = 0)
    Dim rs As New ADODB.Recordset, cn As New ADODB.Connection
    Dim sqlString As String
    ''' setup the connection to the current Worksheet --- this can be changed as needed for a different data source, this example is for EXCEL Worksheet
    cn.Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties='Excel 12.0 Macro;HDR=YES;IMEX=1'")
    '''' Alternate method for the query
    sqlString = "SELECT ParentId, Rank() OVER(PARTITION BY ParentId ORDER BY Label) , vlu.Id, vlu.Label FROM [TransactionalData$] var LEFT JOIN [TransactionalData$] vlu ON vlu.Id=var.ParentId"
    ''' will need to change the TableName (TransactionalData$]
    sqlString = "SELECT DISTINCT " & elementId & " FROM " & tableName & " WHERE " & parentId & " = " & Id
    rs.Open sqlString, cn, adOpenStatic, adLockReadOnly
    '' Start collecting the SQL UNIONs to run at the end
    sqlString = ""
    Do While Not rs.EOF
         '' Add current Element to the UNION
         sqlString = sqlString & "SELECT * FROM " & tableName & " WHERE " & elementId & " = " & rs.Fields(elementId) & " UNION " & vbCrLf
         '' Add all children element to the UNION
         sqlString = sqlString & subQuery(cn, rs.Fields(elementId))
         rs.MoveNext
    Loop
    rs.Close
    '''Debug.Print sqlString
    ''' Remove the extra UNION keyword at the end
    sqlString = Left(sqlString, Len(sqlString) - 8)
    ''' Exectue the built query
    rs.Open sqlString, cn, adOpenStatic, adLockReadOnly
    ''Do While Not rs.EOF
    ''   Debug.Print rs.Fields(elementId) & ", " & rs.Fields(informationalField)
    ''   rs.MoveNext
    ''Loop
End Sub
Function subQuery(cn As ADODB.Connection, Id As Integer) As String
    Dim sqlString As String
    Dim subSqlString As String, rs As New ADODB.Recordset
    '' Create a list of children for the current element
    sqlString = "SELECT DISTINCT " & elementId & " FROM " & tableName & "  WHERE " & parentId & " = " & Id
    rs.Open sqlString, cn, adOpenStatic, adLockReadOnly
    '' start the SQL for current elements children
    sqlString = ""
    Do While Not rs.EOF
        ''' add in the current element to the UNION
        sqlString = sqlString & "SELECT * FROM " & tableName & " WHERE Id = " & rs.Fields(elementId) & " UNION " & vbCrLf
        ''' recursively find additional children for the current element
        sqlString = sqlString & subQuery(cn, rs.Fields(elementId))
        rs.MoveNext
    Loop
    rs.Close
    ''' return the SQL for the current element and all its children
    subQuery = sqlString
End Function

While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value. — Varun Sukheja, Nov 12 '19 at 17:56

(Ms Access) Row_Number() Over Partition

4 Answers4