CODEWITHSUNDEEP
sqlgreatest-n-per-group
coleifer
coleifer

Reputation: 26245

SQL query: how to get users and their three latest posts?

Let's say I have a simple schema of two tables, users and posts. The posts table has a foreign key to users indicating who authored the post.

Also, let's say I want to list the users and their 3 most-recent posts. I can do this in O(n) queries (1 to list users, 1 for each user getting their posts), but how would I do this in O(1) queries?

Either one query to get the users and posts all at once, or 2 queries, one to get the users and one to get the posts. Assume I would de-dupe any repeated user data.

Upvotes: 2

Views: 960

Answers (4)

FuzzyTree
FuzzyTree

Reputation: 32392

A self join that should work on most db's. This assumes that post_time is unique per user. If that's not the case, then you can replace on p2.post_time >= p.post_time with on p2.id >= p.id. 

select u.username, p.id, p.title
from user u join
(
    select p.id, p.title, p.user_id 
    from posts p join posts p2
    on p2.user_id = p.user_id
    and p2.post_time >= p.post_time
    group by p.id, p.title, p.user_id
    having count(*) <= 3
) p on u.id = p.user_id

Upvotes: 1

Brian DeMilia
Brian DeMilia

Reputation: 13248

The following should work in any database:

select u.username, p.title, p.post_time
  from users u
  join posts p
    on u.id = p.user_id
 where p.post_time >=
       (select max(z.post_time)
          from posts z
         where z.user_id = p.user_id
           and z.post_time <
               (select max(y.post_time)
                  from posts y
                 where y.user_id = p.user_id
                   and y.post_time <
                       (select max(x.post_time)
                          from posts x
                         where x.user_id = p.user_id)))

Upvotes: 1

SQL_Lemming
SQL_Lemming

Reputation: 119

SELECT *
FROM Users a
CROSS APPLY (
SELECT TOP 3 *
FROM posts z
WHERE a.User_ID = z.User_ID
ORDER BY z.post_time DESC
) b

Upvotes: 0

user330315
user330315

Reputation:

You didn't state your DBMS, so this is ANSI SQL (supported by a wide range of DBMS):

select *
from (
   select u.username,
          p.title, 
          row_number() over (partition by u.id order by p.post_time desc) as rn
   from users u
     join posts p on u.id = p.user_id
) t
where rn <= 3
order by u.username;

Upvotes: 5

Related Questions