CODEWITHSUNDEEP
sqlpostgresql
Arun
Arun

Reputation: 3680

Select only 3 rows per user - SQL Query

These are the columns in my table

id (autogenerated)
created_user
created_date
post_text

This table has lot of values. I wanted to take latest 3 posts of every created_user

I am new to SQL and need help. I ran the below query in my Postgres database and it is not helpful

SELECT * FROM posts WHERE created_date IN
(SELECT MAX(created_date) FROM posts GROUP BY created_date)

Upvotes: 2

Views: 517

Answers (1)

S-Man
S-Man

Reputation: 23676

You could use the row_number() window function to create an ordered row count per user. After that you can easily filter by this value

demo:db<>fiddle

    SELECT
        *
    FROM (
        SELECT
            *,
            row_number() OVER (PARTITION BY created_user ORDER BY created_date DESC)
        FROM
            posts
    ) s
    WHERE row_number <= 3
  • PARTITION BY groups the users
  • ORDER BY date DESC orders the posts of each user into a descending order to get the most recent as row_count == 1, ...

Upvotes: 2

Related Questions