SQL: How to keep row order with DISTINCT?

Question

The following SQL query:

SELECT messages.id, messages.created_at, comments.created_at FROM messages LEFT JOIN comments ON comments.message_id = messages.id WHERE (messages.id IN (429,443)) ORDER BY GREATEST(messages.created_at, comments.created_at) DESC 

returns:

  id messages.created_at comments.created_at -------------------------------------------------------- 443 2 5 429 1 4 443 2 3 (I replaced dates with numbers for readability) 

To get each id only once, I added DISTINCT :

 SELECT DISTINCT messages.id FROM messages LEFT JOIN comments ON comments.message_id = messages.id WHERE (messages.id IN (429,443)) ORDER BY GREATEST(messages.created_at, comments.created_at) DESC 

But as a result, the id values ​​changed the order:

 id --- 429 443 

What could be the reason for this?

How can I save an order?

Answer 1

The distinct keyword does what it should do, returns a single row with the given column value. Distinct does not allow you to specify which of these lines will be returned, and from the original query it will be clear that such ordering is allowed (there is a line with identifier 443 that follows the line with id 429).

To take control of which rows will be returned, you need to reformulate the query. A typical solution that I will take is to use group by , selecting a group column and the desired row from each group, something like an effect

 SELECT message.id, MAX(message.created_at) FROM message GROUP BY message.id; 

If I need to do more, I will use this kind of query as a subtask in a larger query, perhaps by joining the id field to get more fields from the preferred row, or to organize the query in a certain way.