0

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

TiboK
  • 95
  • 3
  • 12
  • 5
    In the second query, you gave messages an alias M, so you should call it M all over instead of messages. – isaace Jan 18 '18 at 14:20
  • Anyway, the rows in a table (including derived tables, i.e. subqueries) are considered unordered. So your `ORDER BY` is meaningless. What is your DBMS? This is usually solved with window functions or `CROSS APPLY`. From the ugly backticks I take this is MySQL? MySQL doesn't support either method. – Thorsten Kettner Jan 18 '18 at 14:28

3 Answers3

1

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.

unknown
  • 461
  • 9
  • 23
0

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
);
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
0

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;
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73