How to get limited number of rows based on a DISTINCT values-CodePudding

I have table like this

id | value |       date         |  for  | unit
----------------------------------------------
17 | 49.0 | 2021-02-22 10:00:00 | Chest | cm
29 | 49.0 | 2021-02-22 10:00:00 | Hip   | cm
14 | 49.0 | 2021-02-21 10:00:00 | Chest | cm
16 | 48.0 | 2021-02-21 09:00:00 | Chest | cm
26 | 49.0 | 2021-02-21 10:00:00 | Waist | cm
28 | 48.0 | 2021-02-21 10:00:00 | Hip   | cm
27 | 48.0 | 2021-02-20 10:00:00 | Waist | cm
13 | 49.0 | 2021-02-06 10:00:00 | Chest | cm
25 | 49.0 | 2021-02-06 10:00:00 | Hip   | cm
12 | 48.0 | 2021-02-05 10:00:00 | Chest | cm

The result set should return:

id | value |       date         |  for  | unit
----------------------------------------------
17 | 49.0 | 2021-02-22 10:00:00 | Chest | cm
14 | 49.0 | 2021-02-21 10:00:00 | Chest | cm
29 | 49.0 | 2021-02-22 10:00:00 | Hip   | cm
28 | 48.0 | 2021-02-21 10:00:00 | Hip   | cm
26 | 49.0 | 2021-02-21 10:00:00 | Waist | cm
27 | 48.0 | 2021-02-20 10:00:00 | Waist | cm

This output contains 2 rows of each distinct values in 'for' column sorted desc by date

CodePudding user response：

Easy to do with a window function like row_number() to rank each group, and then taking just the first two:

WITH ranked AS
  (SELECT id, value, date, for, unit,
          row_number() OVER (PARTITION BY for ORDER BY date DESC) AS rn
   FROM mytable)
SELECT id, value, date, for, unit
FROM ranked
WHERE rn <= 2
ORDER BY for, date DESC;

gives

id  value  date                 for    unit
--  -----  -------------------  -----  ----
17  49.0   2021-02-22 10:00:00  Chest  cm
14  49.0   2021-02-21 10:00:00  Chest  cm
29  49.0   2021-02-22 10:00:00  Hip    cm
28  48.0   2021-02-21 10:00:00  Hip    cm
26  49.0   2021-02-21 10:00:00  Waist  cm
27  48.0   2021-02-20 10:00:00  Waist  cm

For best results, add an index on mytable(for, date DESC) if this will be run frequently.