Problem with a query using group by

User, date Message
Written by joao.msdn
10 months ago
Category: General
1 posts since Thu, 26 Jul 12 		Hello, I'm with the following question, I need to make a query involving 3 tables

user, chat_room and message

the table message have foreign key from tables chat_room and user

the system consists in a simple chat room


the query must return the last row of each user from a specific chat_room
I'm trying this way

select u.id, u.nick, m.messageHour
from user u

inner join message m on u.id = m.id_user
inner join chat_room c on m.id_chat_room = c.id

where c.id = 148 && u.active = true && u.id <> 1

group by m.id_user
order by c.messageHour desc




but unfortunately, the query is returning the FIRST row of each user and i need the LAST

Does someone knows a way to do this? thanks a lot
Written by ansgar
10 months ago
3972 posts since Fri, 07 Apr 06 		Is that MS SQL, or? That "&&" operator and "u.active=true" should not work on MySQL.

You will need some sub select to get the last row. And you can test if you see all values using GROUP_CONCAT:

SELECT ... GROUP_CONCAT(m.messageHour)
FROM ...



But that won't work in MS SQL I guess.
 

Please login to leave a reply, or register at first.