CODEWITHSUNDEEP
phpmysqlsql
Naveen
Naveen

Reputation: 125

How do I select the latest post for a user in a mysql DB?

I have the following table setup:

table name posts

Id, user_id, title, content, date

Now i need to select the lastest(last) post of each user from above table Can anyone help how can we do this?

right now i am using

SELECT * FROM `posts` GROUP BY `user_id`
ORDER BY `ID` asc

but actually it just fetches the first post of each user but i need the last post of each user

Upvotes: 4

Views: 1240

Answers (4)

Quassnoi
Quassnoi

Reputation: 425371

SELECT  p.*
FROM    (
        SELECT  DISTINCT  user_id
        FROM    posts
        ) pd
JOIN    posts p
ON      p.id = 
        (
        SELECT  id
        FROM    posts pi
        WHERE   pi.user_id = pd.user_id
        ORDER BY
                user_id DESC, date DESC, id DEST
        LIMIT 1
        )

Create an index on posts (user_id, date, id) for this to work fast.

See examples 4 and 3 here:

which explain why your original query will not work.

Upvotes: 1

Gonzalo Larralde
Gonzalo Larralde

Reputation: 3541

Ok, this is ugly, but it works.

MySQL has a handy quirkable mode of group columns. In other DB engines, like MSSQL, if you're using GROUP BY, every column in SELECT must be grouped, or should be an aggregation function.

What MySQL does, actually, is bring up the first record used to group by. So, if you do this:

SELECT * FROM posts GROUP BY user_id ORDER BY ID desc

Should work. But, if it doesn't, it means that it first groups and ignores the order, so, you can try something like this:

SELECT * FROM (SELECT * FROM posts WHERE 1 = 1 ORDER BY ID desc) GROUP BY user_id

It's important to put you filters inside the subquery, in order to keep performance in acceptable levels.

Good luck!

Upvotes: -2

derobert
derobert

Reputation: 51147

Assuming posts are assigned in an increasing order:

SELECT max(post_id) AS post_id FROM posts GROUP BY user_id;

if no such assumption can be made, you can do something like:

SELECT user_id, max(date) AS most_recent FROM posts GROUP BY user_id

you can then use that to grab the full post:

SELECT p1.*
  FROM
    (SELECT max(post_id) AS post_id FROM posts GROUP BY user_id) p2
    JOIN p1 ON (p1.post_id = p2.post_id);

or for the no assumption case:

SELECT p1.*
  FROM
    (SELECT user_id, max(date) AS most_recent FROM posts GROUP BY user_id) p2
    JOIN p1 ON (p1.user_id = p2.user_id AND p2.most_recent = p1.date);

You will get multiple posts from a user if the most recent posts both have the same date. Arguably, that is correct, as neither is newer.

Note: These can be written as correlated subqueries as well. Benchmarking will tell you which perform best in your particular case.

Upvotes: 4

Harish
Harish

Reputation: 2324

okey this might be your problem do one thing

    SELECT * FROM 
   (SELECT * FROM posts ORDER BY date desc) AS `result` 
    GROUP BY `result`.`user_id`

As group by user_id is used earlier your example it will take only the first id check this it will work!

Upvotes: 0

Related Questions