Re-writing this sql query

Question

Hi, I have written an SQL query that gets unread messages but I think I can improve the code (speed and readability). The first select is for COUNT function, the second is for grouping messages according to conversation_id, and the final nested select is for selecting last messages.

Please give me suggestions. 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

Does that even work? id isn't in an aggregate, so you can't select ID group by conversation.

That being said, you're looking for conversations that have unread messages?

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

should get you what you need

Answer 2

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 don't need the order by clause, and you can move the group by clause into the inner subquery.

Answer 3

The query as-is will not work - you need to define all columns that aren't wrapped in aggregates in the GROUP BY.

It's not clear, but if you want a count of 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 != ?

• The subqueries are unnecessary
• The ORDER BY is a waste of resources because it is not used in the ultimate output nor is TOP being used
• The GROUP BY won't work because MESSAGES.id is not in the list of columns