CODEWITHSUNDEEP
phpmysql
Emanuel Ones
Emanuel Ones

Reputation: 289

MySQL joining tables and returning the latest row from the second table when the comparison is made between identical values

I have table 1: users

id | name |

1  | john |
2  | garry|
3  | sam  |

And table two called posts:

id | user_id |     title    |   posted

1  |    1    | Something    | 1551128761
2  |    1    | Else         | 1551128761
3  |    3    | Some Title   | 1551122745
4  |    2    | Demo Title   | 1551129777
5  |    3    | Something    | 1551126793

user_id in the second table is the id of the user in the first table

I need to get the latest post out of the table and i'm doing that currently by using this query:

SELECT u.id, u.name, p.title 
FROM users AS u 
LEFT JOIN posts AS p 
ON p.user_id= u.id 
WHERE p.posted = ( SELECT MAX(posted) FROM posts WHERE user_id = u.id )
ORDER BY u.id 
LIMIT 15

But the problem with this query is that if the timestamp is the same for the same user (in this example for user with user_id 1 the timestamp is the same) i'm getting both of those rows instead of just the latest one(the latest one has the highest id)

Upvotes: 1

Views: 75

Answers (3)

Satish Saini
Satish Saini

Reputation: 2968

Try this MySQL query:

SELECT u.id, 
       u.name, 
       p.title 
FROM   users AS u 
       JOIN posts AS p 
         ON p.id = (SELECT pi.id 
                    FROM   posts AS pi 
                    WHERE  pi.user_id = u.id 
                    ORDER  BY pi.id DESC 
                    LIMIT  1);

Tested and works fine. Here is a demo: DBFiddle

To speed up select query, consider adding indexes

ALTER TABLE posts ADD INDEX(user_id);
ALTER TABLE posts ADD INDEX(posted);

Upvotes: 2

lumonald
lumonald

Reputation: 364

How about restructuring the query slightly?

SELECT posts.title, users.id, users.name
FROM posts, users 
WHERE posts.user_id = users.id 
ORDER BY posts.posted DESC, posts.id DESC
LIMIT 1

Essentially selecting from posts, ordering by the posted timestamp and secondly the id of the post in descending order in case timestamp is the same.

Upvotes: 0

vmaroli
vmaroli

Reputation: 623

One option using id column from posts table as following. This is assuming id is going to be different for each post record is posts table. Demo here

SELECT u.id, u.name, p.title,p.posted
FROM users AS u 
LEFT JOIN posts AS p 
ON p.user_id= u.id 
WHERE (p.posted,p.id) = ( SELECT MAX(posted),MAX(id) FROM posts WHERE user_id = u.id )
ORDER BY u.id

Upvotes: 1

Related Questions