CODEWITHSUNDEEP
sqlms-accessms-officems-access-2013
Filiep Samyn
Filiep Samyn

Reputation: 23

Retrieve last N records from a table

I have searched but not found an answer for my question.

I have a table orders that consists of

I want to retrieve the last N order dates for each client in a single view

Of course I could use SELECT TOP N date FROM orders WHERE client = 'xx' ORDER DESC and then use UNION for the different values for client. The problem is that with changes in client base the statement would require revision and that the UNION statement is impractical with a large client base.

As an additional requirement this needs to work in Access SQL.

Upvotes: 1

Views: 2123

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

I think the following will work in MS Access:

select t.*
from table as t
where t.date in (select top N t2.date
                 from table as t2
                 where t2.client_id = t.client_id
                 order by t2.date desc
                );

One problem with MS Access is that top N will retrieve more than N records if there are ties. If you want exactly "N", then you can use order by date, id in the subquery.

Upvotes: 1

Heinzi
Heinzi

Reputation: 172220

Step 1: Create a query that yields a rank order by date per client for every row. Since Access SQL does not have ROW_NUMBER() OVER (...) like SQL Server, you can simulate this by using the technique described in the following question:

If you have done step 1 correctly, your result should be as follows:

id   client_id    date      rank
----------------------------------
        1       2014-12-01   7 
        1       2014-12-02   6 
        1       2014-12-05   5 
        1       2014-12-07   4 
        1       2014-12-11   3 
        1       2014-12-14   2 
        1       2014-12-15   1 
        2       2014-12-01   2 
        2       2014-12-02   1
       ...

Step 2: Use the result from step 1 as a subquery and filter the result such that only records with rank <= N are returned.

Upvotes: 1

Related Questions