Problem with a query using group by

[expired user #6506]'s profile image [expired user #6506] posted 12 years ago in General Permalink
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
ansgar's profile image ansgar posted 12 years ago Permalink
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.