select the rows that have more that X occurrences of the same foreign key and ignore the X most rece-CodePudding

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 > ?;

CodePudding user response：

Use COUNT() and ROW_NUMBER() window functions:

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 > ?;

Replace ? with the values that you want.

But, if the same number X is applied for both the number of total rows for each fk and the number of rows to be dismissed, the query can be simplified:

SELECT id, fk, timestamp 
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY fk ORDER BY timestamp DESC) rn
  FROM tablename
) t
WHERE rn > ?;

See the demo.

CodePudding user response：

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