Re-writing this sql query

Question

I wrote an SQL query that receives unread messages, but I think I can improve the code (speed and readability). The first selection is for the COUNT function, the second is for grouping messages according to the name chat_id, and the final sub-selection is for selecting the latest messages.

Please give me some advice. Thanks in advance.

SELECT COUNT(*) as unreaded FROM ( 
  SELECT id 
  FROM (
    SELECT id, conversation_id
    FROM messages
    WHERE to_id = ?
    AND readed = 0
    and NOT hide_from = ?
    ORDER BY sended DESC
  ) AS temp_messages 
  GROUP BY conversation_id
) as temp_messages2

Answer 1

The as-is query will not work - you need to identify all columns that are not aggregated in GROUP BY.

Not clear, but if you want to count unique conversations, use:

SELECT COUNT(DISTINCT m.conversation_id) AS unread
  FROM MESSAGES m
 WHERE m.to_id = ?
   AND m.readed = 0
   AND m.hide_from != ?

... otherwise use:

SELECT COUNT(*) AS unread
  FROM MESSAGES m
 WHERE m.to_id = ?
   AND m.readed = 0
   AND m.hide_from != ?

• No subqueries needed
• ORDER BY - , TOP
• GROUP BY , MESSAGES.id

Answer 2

? id , .

, ?

select count(distinct conversation_id) from message
 WHERE to_id = ? 
   AND readed = 0 
   and NOT hide_from = ? 

,

Answer 3

SELECT COUNT(*) as unreaded FROM ( 
  SELECT id, conversation_id
  FROM messages
  WHERE to_id = ?
  AND readed = 0
  and NOT hide_from = ?
  GROUP BY conversation_id
) as temp_messages2

You do not need an offer order by, and you can move the offer group byto an internal subquery.