Let's say I have a query that returns the following data:
id date
-- ------
1 2015-01-12
1 ... // here I might have X more rows
1 2015-06-30
2 2015-01-12
2 ... // here I might have Y more rows
2 2015-05-20
...
Given that X, Y >= 120 and X != Y and the order of the query is id, date ASC I want a way to retrieve the record number 120 for id 1 and the 120 for the id 2 (and so on for each different ID), something like:
id date
-- --------
1 2015-03-24 // this is the record 120 for id = 1
2 2015-04-26 // this is the record 120 for id = 2
...
Notice that the dates don't follow a sequential order (you may have a gap between one row and the next one).
Is there a direct SQL solution for my problem? (I know I can use vba to achieve my goal but I rather stay with SQL)
As a clarification note, see this example. Given the following resultset:
id date
-- ------
1 2015-01-12 // this is record 1 for id = 1
1 2015-01-13 // this is record 2 for id = 1
1 2015-01-20 // this is record 3 for id = 1
1 2015-01-21 // this is record 4 for id = 1
...
1 2015-03-22 // this is record 118 for id = 1
1 2015-03-23 // this is record 119 for id = 1
1 2015-03-24 // this is record 120 for id = 1
1 2015-03-25 // this is record 121 for id = 1
...
1 2015-06-30 // this is the last row for id = 1
2 2015-01-12 // this is record 1 for id = 2
2 2015-01-13 // this is record 2 for id = 2
...
2 2015-04-25 // this is record 120 for id = 2
...
2 2015-05-20 // this is the last record for id = 2
The result should be:
id date
-- --------
1 2015-03-24
2 2015-04-26
Remember, I have at least 120 records for each ID, this is a fact (I have a query that gives only the IDs with more than 119 records)
I've tried to play with the SELECT TOP directive, but I fail to achieve the results I want as I cannot apply it direcly: I don't want the top 120 and then get the last row as I want the last one of the TOP 120 for each ID.
My goal would be to have something like:
SELECT id, 120thRow(date)
FROM table
GROUP BY id;
unfortunatelly I don't know how to implement the 120thRow function in access.
In the end, I've managed a way to put a row counter for each day and ID like so:
select id, date,
(
select count(date)
from table As t1
where t1.id = t.id
and t1.date <= t.date
) As rowNum
from table As t
From here on it's just a matter of selecting from this resultset the rows which has rownum = 120 and game over.