Postgresql limit column to 3 per value

Question

    SELECT u.id,
            'Shift' AS which,
             se.created_at
    FROM users AS u
    JOIN schedule_elements AS se ON se.owner_id = u.id
    UNION ALL
            (SELECT u.id,
                         'Like' AS which,
                         ll.created_at
            FROM users AS u
            JOIN likes AS ll ON ll.owner_id = u.id
            UNION
            SELECT u.id,
                         'Comment' AS which,
                         cm.created_at
            FROM users AS u
            JOIN comments AS cm ON cm.owner_id = u.id)
    ORDER BY ID DESC , created_at DESC


The output looks like:
id, which, created_at
555, shift, <date>
555, shift, <date>
555, comment, <date>
555, shift, <date>
555, like, <date>
333, shift, <date>
333, shift, <date>
333, comment, <date>
333, shift, <date>
111, like, <date>
111, shift, <date>
111, shift, <date>

The output has 5 entries for id 555, and 4 for 333. I want to filter this query so that I only have the top 3 entries for 555, top 3 for 333, etc..

pottedmeat7 · Answer 1 · 2017-02-10T16:24:59.723

0

Use a Limit Clause https://www.postgresql.org/docs/current/static/queries-limit.html

    (SELECT u.id,'Shift' AS which,se.created_at
    FROM users AS u
    JOIN schedule_elements AS se ON se.owner_id = u.id ORDER BY u.id DESC , u.created_at DESC LIMIT 3)
    UNION
    (SELECT u.id,'Like' AS which,ll.created_at
    FROM users AS u
    JOIN likes AS ll ON ll.owner_id = u.id ORDER BY u.id DESC , u.created_at DESC LIMIT 3)
    UNION
    (SELECT u.id,'Comment' AS which,cm.created_at
    FROM users AS u
    JOIN comments AS cm ON cm.owner_id = u.id ORDER BY u.id DESC , u.created_at DESC LIMIT 3)

edited Feb 10 '17 at 16:24

answered Feb 09 '17 at 23:42

pottedmeat7

78
1
7

Looks like this query will not works - you can use LIMIT only for whole statement (all 3 union). And even it works it will shows 3 items per each SELECT not for all. – Roman Tkachuk Feb 10 '17 at 06:36
Looks like it works here http://stackoverflow.com/questions/1415328/combining-union-and-limit-operations-in-mysql-query – pottedmeat7 Feb 10 '17 at 16:14
that is completely different task. There needs 10 records per two different company. Here - 3 last operations over user + their comments and likes. – Roman Tkachuk Feb 10 '17 at 16:17
And if you about LIMIT for statements - you need use () for statement with it. – Roman Tkachuk Feb 10 '17 at 16:19
I think its because LIMIT comes before the ORDER BY clause. So The ordering would have to come before you call LIMIT. I'll edit my answer. – pottedmeat7 Feb 10 '17 at 16:19
Your query give last 3 operations for EACH table. Not for whole list of operations. Do you try your query on data provided in question? – Roman Tkachuk Feb 10 '17 at 16:22

Roman Tkachuk · Answer 2 · 2017-02-10T09:48:49.580

Probably you need something like this. For p in CTE I use your query. In p2 I set row_number for all rows by windowing function and after that can show only first/last 3 per user.

 WITH p AS (
    SELECT u.id,
            'Shift' AS which,
             se.created_at
    FROM users AS u
    JOIN schedule_elements AS se ON se.owner_id = u.id
    UNION ALL
    SELECT u.id,
           'Like' AS which,
             ll.created_at
    FROM users AS u
    JOIN likes AS ll ON ll.owner_id = u.id
    UNION ALL
    SELECT u.id,
         'Comment' AS which,
         cm.created_at
    FROM users AS u
    JOIN comments AS cm ON cm.owner_id = u.id
), p2 AS (
   SELECT *,
        row_number() OVER (PARTITION BY id ORDER BY created_at DESC) AS rank
   FROM p
   WHERE which IS NOT NULL
)
SELECT id, which, created_at --, rank
FROM p2
WHERE rank <= 3
ORDER BY ID DESC , created_at DESC;

If my answer help you, please mark your question as resolved and vote up me. Thanks — Roman Tkachuk, Feb 21 '17 at 21:24

Postgresql limit column to 3 per value

2 Answers2