CODEWITHSUNDEEP
mysqlsql
user1409508
user1409508

Reputation: 623

MySQL: Order by with joins and group by

I have following tables:

message_notification

 ________________________
| id | message   | user  |
|____|___________|_______|
|  1 |     10    |   1   |
|____|___________|_______|
|  2 |     11    |   1   |
|____|___________|_______|
|  3 |     12    |   1   |
|____|___________|_______|

message

 _________________________________________
| id |    post   |         date           |
|____|___________|________________________|
| 10 |     10    |   15-11-2015 19:45:36  |
|____|___________|________________________|
| 11 |     20    |   15-11-2015 19:47:44  |
|____|___________|________________________|
| 12 |     10    |   15-11-2015 19:53:12  |  
|____|___________|________________________|

post

 ________________
| id |  content  |
|____|___________|
| 10 |     XX    |
|____|___________|
| 20 |     XX    |
|____|___________|

I would like to get notifications grouped by post and ordered by posts with the newest messages.

So, I would like to get following results: 

1. post #10    
2. post #20

and if someone write new message in post #20, results will be following:

1. post #20   
2. post #10

I have tried following query, but seems like order by doesn't work with group by or inner joins

SELECT noti.*
FROM message_notification noti
INNER JOIN message mess ON mess.id = noti.message
INNER JOIN post ON post.id = mess.post
WHERE noti.user = 1
GROUP BY post.id
ORDER BY mess.date DESC

Upvotes: 0

Views: 50

Answers (3)

FuzzyTree
FuzzyTree

Reputation: 32392

You can use not exists to only select the most recent message per post

select * from post p
join message m on m.post = p.id
join message_notification mn on mn.message = m.id
where mn.user = 1
and not exists (
    select 1 from message m2
    join message_notification mn2 on mn2.message = m2.id        
    where m2.post = m.post
    and mn2.user = 1
    and m2.date > m.date
)
order by m.date desc

Upvotes: 1

Subin Chalil
Subin Chalil

Reputation: 3660

First you should filter out the later date(maximum date) from message for each post. Please try this out.

SELECT noti.*
FROM message_notification noti
INNER JOIN (SELECT message.* 
           FROM message
           INNER JOIN 
          (SELECT id,MAX(`date`) AS dater 
           FROM message 
           GROUP BY id) T ON T.`dater` = message.`date`) mess ON mess.id = noti.message
INNER JOIN post ON post.id = mess.post
WHERE noti.user = 1
GROUP BY post.id
ORDER BY mess.date DESC

Hope this helps.

Upvotes: 1

Leandro Baldi
Leandro Baldi

Reputation: 35

You are not using any function. ORDER BY post and date its enought. Remember all columns in SELECT clause have to be in GROUP BY clause.

Upvotes: 1

Related Questions