Get the last 5 distinct values for each ID

Question

I'm working with PostgreSQL 9.4.

I have a table that contains the following entries:

 id | postcode | date_created
 ---+----------+-----------------
 14 | al2 2qp  | 2015-09-23 14:46:57
 14 | al2 2qp  | 2015-09-23 14:51:07
 14 | sp2 8ag  | 2015-09-23 14:56:11
 14 | se4      | 2015-09-23 16:12:05
 17 | e2       | 2015-09-23 16:15:35
 17 | fk20 8ru | 2015-09-23 16:28:35
 17 | fk20 8ru | 2015-09-23 16:35:51
 17 | se2      | 2015-09-23 16:36:17
 17 | fk20 8ru | 2015-09-23 16:36:22
 17 | fk20 8ru | 2015-09-23 16:37:04
 17 | se1      | 2015-09-23 16:37:11
 17 | fk20 8ru | 2015-09-23 16:37:15
 17 | se1 8ga  | 2015-09-24 09:52:46
 17 | se1      | 2015-09-24 10:01:19
 17 | hp27 9rz | 2015-09-24 10:05:27
 17 | hp27 9rz | 2015-09-24 10:05:29
 17 | se1      | 2015-09-24 10:19:46
 14 | tn21 8qb | 2015-09-24 14:49:05
 14 | tn21 8qb | 2015-09-24 15:42:45
 14 | tn21 8qb | 2015-09-24 17:38:06
 14 | n4 1ny   | 2015-09-25 14:49:10

What I want to achieve is a query that returns the 5 most recent unique postcode records for each id:

 id | postcode
 ---+---------
 14 | n4 1ny
 14 | tn21 8qb
 14 | se4
 14 | sp2 8ag
 14 | al2 2qp
 17 | se1
 17 | hp27 9rz
 17 | se1 8ga
 17 | fk20 8ru
 17 | se2

What would be the best way of achieving this? I've been playing around with subqueries but keep hitting walls when it comes to ordering them whilst doing a DISTINCT and GROUP BY.

Answer 1

There are probably many ways to do this. The first that comes to mind is to use window functions:

SELECT 
    id, postcode
FROM
  ( SELECT id, postcode, 
           ROW_NUMBER() OVER (PARTITION BY id
                              ORDER BY MAX(date_created) DESC
                             ) AS rn
    FROM tablename
    GROUP BY id, postcode
  ) AS t
WHERE
    rn <= 5
ORDER BY 
    id, rn ;

Test at SQLfiddle.

If there are ties, say the 5th, 6th and 7th postcode for an id have the same date_created, only one of them (choice will be arbitrary) will be in the results. If you want all the tied postcodes in those cases, use RANK() instead of ROW_NUMBER().

---

Another option is to use the LATERAL syntax. I'm not sure which will be more efficient, it wil probably depend on the values distribution of the two columns (id and postcode), i.e. how many distinct ids in the whole table, how many distinct postcodes per id and how many rows per (id, postcode) combinations.

SELECT 
    t.id, ti.postcode
FROM
    ( SELECT DISTINCT id
      FROM tablename
    ) AS t
  CROSS JOIN LATERAL
    ( SELECT tt.postcode,
             MAX(tt.date_created) AS date_created
      FROM tablename AS tt
      WHERE tt.id = t.id
      GROUP BY tt.postcode
      ORDER BY date_created DESC
      LIMIT 5
    ) AS ti 
ORDER BY 
    t.id, ti.date_created DESC;

Adding an index on (id, postcode, date_created) would be a good idea, too - or on (id, postcode, date_created DESC).

Answer 2

Typically you would have another table (let's name it tbl) with all distinct id values in separate rows. If you don't, create it:

CREATE TABLE tbl AS 
SELECT DISTINCT id FROM postcode ORDER BY id;  -- ORDER is optional

Or replace tbl with in below query with the same SELECT as subquery, but that's (much) more expensive.

If there can be many rows per id, a recursive CTE should be fastest:

WITH RECURSIVE cte AS (
   SELECT t.id, 1 AS rnk, p.*, ARRAY[postcode] AS arr
   FROM   tbl t
        , LATERAL (
      SELECT postcode, date_created
      FROM   postcode
      WHERE  id = t.id
      ORDER  BY date_created DESC NULLS LAST
      LIMIT  1
      ) p

   UNION ALL
   SELECT t.id, rnk + 1, p.*, arr || p.postcode
   FROM   cte t
        , LATERAL (
      SELECT postcode, date_created
      FROM   postcode
      WHERE  id = t.id
      AND    date_created < t.date_created
      AND    postcode <> ALL (t.arr)
      ORDER  BY date_created DESC NULLS LAST
      LIMIT  1
      ) p
   WHERE rnk < 5
   )
SELECT id, rnk, postcode, date_created
FROM   cte
ORDER  BY id, rnk;

Assuming postcode to be text or varchar. There can be issues in this particular query if postcode has type modifiers (like varchar(50) or similar):

• Surprising results for data types with type modifier

An index on (id, date_created) is essential for performance with big tables:

CREATE INDEX postcode_foo_idx ON postcode(id, date_created DESC NULLS LAST);

SQL Fiddle.

You can skip NULLS LAST everywhere if date_created is defined NOT NULL.

If substantially more than 5 rows per id is a rare case, @ypercube's queries will be faster. Test with EXPLAIN ANALYZE.

The difference: my rCTE comes with more overhead, but performance is hardly influenced by older surplus rows (those are not touched in the query). Both of @ypercube's queries have less overhead, but get slower with more rows per id.

Basics with links and more explanation:

• Optimize GROUP BY query to retrieve latest record per user

If you don't have a table tbl, you can use a similar technique to distill distinct id from postcode in the first place:

• Query last N related rows per row