Using LATERAL JOIN To Get Top N per Group

Description

Given the schema presented below write a query, which uses a LATERAL join, that returns two most viewed posts for every category.

Order the result set by:

  1. category name alphabetically
  2. number of post views largest to lowest
  3. post id lowest to largest

Note:

  • Some categories may have less than two or no posts at all.
  • Two or more posts within the category can be tied by (have the same) the number of views. Use post id as a tie breaker — a post with a lower id gets a higher rank.

Hint: “Some categories may have less than two or no posts at all.” -> Use left join!

Schema

Column     | Type                        | Modifiers
------------+-----------------------------+----------
id | integer | not null
category | character varying(255) | not null
Column     | Type                        | Modifiers
------------+-----------------------------+----------
id | integer | not null
category_id | integer | not null
title | character varying(255) | not null
views | integer | not null

Desired Output

The desired output should look like this:

category_id | category | title                             | views | post_id
------------+----------+-----------------------------------+-------+--------
5 | art | Most viewed post about Art | 9234 | 234
5 | art | Second most viewed post about Art | 9234 | 712
2 | business | NULL | NULL | NULL
7 | sport | Most viewed post about Sport | 10 | 126
...
  • category_id - category id
  • category - category name
  • title - post title
  • views - the number of post views
  • post_id - post id

Solution:

select c.id as category_id,
c.category,
p.post_id,
p.views,
p.title
from categories c
left join lateral
(select id as post_id,
category_id,
title,
views
from posts where category_id = c.id
order by views desc, id
limit 2) p
on c.id = p.category_id
order by c.category, p.views desc, p.post_id;

Other Solution:

SELECT c.id category_id, c.category, p.title, p.views, p.id post_id
FROM categories c
LEFT JOIN LATERAL(
SELECT title, views, id
FROM posts
WHERE category_id = c.id
ORDER BY views DESC
LIMIT 2) p ON true
ORDER BY 2, 4 DESC, 5;

Link

Reference

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store