'select the rows that have more that X occurrences of the same foreign key and ignore the X most recent (timestamp)
I have a table with a property that is a foreign key and another property that is a timestamp
Table
| id | fk | timestamp |
|---|---|---|
| 1 | 2 | 16-02-2022 |
| 2 | 2 | 01-02-2022 |
| 3 | 2 | 02-02-2021 |
| 4 | 3 | 24-05-2020 |
| 5 | 3 | 11-01-2022 |
| 6 | 3 | 16-09-2021 |
| 7 | 3 | 01-01-2022 |
I want to select the rows that have more that X ocurrences of the same foreign key and i want to ignore the X most recent(timestamp) elements for each foreign key
So basically with a X of 2 the select would return
| id | fk | timestamp |
|---|---|---|
| 3 | 2 | 02-02-2021 |
| 6 | 3 | 16-09-2021 |
| 4 | 3 | 24-05-2020 |
And with a X of 3 the select would return
| id | fk | timestamp |
|---|---|---|
| 4 | 3 | 24-05-2020 |
Edit:
Thanks alot @forpas for the awesome aproach
Solution
SELECT id, fk, timestamp
FROM (
SELECT *,
COUNT(*) OVER (PARTITION BY fk) counter,
ROW_NUMBER() OVER (PARTITION BY fk ORDER BY timestamp DESC) rn
FROM tablename
) t
WHERE counter > ? AND rn > ?;
Solution 1:[1]
SELECT
id, fk, timestamp
FROM
(
SELECT id, fk, timestamp, ROW_NUMBER() OVER(PARTITION BY fk ORDER BY timestamp ASC) AS X,
COUNT(1) OVER(PARTITION BY fk) AS Total
FROM MyTable
) AS S
WHERE A.Total - S.X >= @X
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 |
