CODEWITHSUNDEEP
mysqlsqlsql-order-by
TiboK
TiboK

Reputation: 95

ORDER BY before GROUP BY with JOIN

I have a problem with my SQL query. I want to display the last message for a user. So, i use the method "GROUP BY" but he don't display the LAST message.

This is my first query:

SELECT `messages`.*, `conversations`.* 
FROM `messages` JOIN `conversations` ON `conversations`.`cNUM` = `messages`.`mCONV_NUM` 
WHERE `cUSER2` = '2' 
GROUP BY `messages`.`mCONV_NUMn`

I try to follow this subject: ORDER BY date and time BEFORE GROUP BY name in mysql (and a lot of other)

And i have this: 

SELECT `messages`.*, `conversations`.* 
FROM (SELECT mTIME 
      FROM `messages` 
      ORDER BY mTIME desc) AS M 
JOIN `conversations` ON `conversations`.`cNUM` = `messages`.`mCONV_NUM` 
WHERE `cUSER2` = '2' 
GROUP BY `messages`.`mCONV_NUMn`

And i have this error: Table 'messages' unknown.

So.. i need your help guys

Upvotes: 0

Views: 220

Answers (3)

unknown
unknown

Reputation: 461

You gave an alias M to your messages table, as isaace said you shall refer to it as 'M' in the rest of the query as this temporary name lasts for the whole duration of the query and FROM is an initial phase in the query processing.

We are talking about something called logical query processing, this means that in your query FROM statement is evaluated and processed initially and then the rest of query.

In LQP terms queries will be processed in the following order.

  • FROM --> WHERE --> GROUP BY --> HAVING --> SELECT --> ORDER BY

(Of course I left out some phases but you get the idea)

Also you can use LIMIT to get the last message for a user. Just add LIMIT 1 at the end.

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

You say you want the last message for a user, but it seems you really want several messages, namely their last message of each conversation they took part in.

For the lack of CROSS APPLY and window functions in MySQL, you need a query that does a lot of lookups to get the last message per conversation:

select *
from conversations c 
join messages m on m.mconv_num = c.cnum
where c.cuser2 = 2
and not exists 
(
  select * 
  from messages m2 
  where m2.mconv_num = m.mconv_num
  and m2.mtime > m.mtime
);

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

I want to display the last message for a user.

You get the one last message for a user with LIMIT:

select *
from conversations c 
join messages m on m.mconv_num = c.cnum
where c.cuser2 = 2
order by m.mtime
limit 1;

Upvotes: 0

Related Questions