Skip to content
Advertisement

Getting the all rows of with any of the lastest N tags

I need to get the all the records of the N latest Tags, i.e. ordered by timestamp.

I’ve managed to construct the following query that retrieves the latest 20 tags. However when I try to join on the result, the “limit” will affect the total number of rows, and not the number of unique tags.

SELECT id, tag, timestamp
FROM items AS T1 
WHERE NOT EXISTS (
        SELECT *
        FROM items AS T2
        WHERE T2.tag = T1.id AND T2.timestamp > T1.timestamp
    )
ORDER BY timestamp DESC 
LIMIT 20;

So given the following table…

 id | tag        | timestamp
----+------------+------------
  1 | Green      | 2019-04-30
  2 | Red        | 2019-04-29
  3 | Blue       | 2019-04-28
  4 | Green      | 2019-04-27
  5 | White      | 2019-04-26
  6 | Red        | 2019-04-25
  7 | Black      | 2019-04-24
  8 | Blue       | 2019-04-23
  9 | Red        | 2019-04-22
 10 | Black      | 2019-04-21

… A query for the lastest 3 latests tags should output the following or similar:

 id | tag        | timestamp
----+------------+------------
  1 | Green      | 2019-04-30
  4 | Green      | 2019-04-27
  2 | Red        | 2019-04-29
  6 | Red        | 2019-04-25
  9 | Red        | 2019-04-22
  3 | Blue       | 2019-04-28
  8 | Blue       | 2019-04-23

Advertisement

Answer

If you want the most recent 20, with duplicates:

SELECT id, tag, timestamp
FROM items i
WHERE i.tag IN (SELECT i2.tag
                FROM i2
                ORDER BY timestamp DESC
                FETCH FIRST 20 ROWS ONLY
               );
ORDER BY timestamp DESC ;

If you want the most recent 20 without duplicates:

SELECT id, tag, timestamp
FROM items i
WHERE i.tag IN (SELECT i2.tag
                FROM i2
                GROUP BY i2.tag
                ORDER BY MAX(timestamp) DESC
                FETCH FIRST 20 ROWS ONLY
               );
ORDER BY timestamp DESC 
5 People found this is helpful
Advertisement