SQL:如何使用DISTINCT保持行顺序? [英] SQL: How to keep rows order with DISTINCT?

问题描述

以下SQL查询:

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

返回:

 id         messages.created_at     comments.created_at
--------------------------------------------------------
 443                2                       5
 429                1                       4
 443                2                       3

 (I replaced dates with numbers for readability)

仅在添加DISTINCT后获得每个id:

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

但是，结果id值更改了顺序:

But, in the result the id values changed order:

id
---
429
443

这可能是什么原因?

我如何保留订单?

推荐答案

distinct关键字正在执行应做的事情，返回一行，每行具有给定的列值. Distinct不允许您指定要返回的哪一个，并且从原始查询中可以清楚地看到这样的排序是允许的(在ID为429的行之后有ID为443的行) ).

the distinct key word is doing what it's supposed to do, return one row each with a given column value. Distinct doesn't allow you to specify which such row will be returned, and it's clear from the original query that such an ordering is allowed (there is a row with id 443 that follows a row with id 429).

要控制将返回的行，您需要重新构造查询.我将采用的典型解决方案是使用group by，从每个组中选择组列和所需的行，以达到

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

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

如果需要执行更多操作，则将在较大的查询中将这种查询用作子选择，可能会加入id字段以从首选行中获取更多字段，或者以特定方式对查询进行排序.

If I need to do more, I'll use this sort of query as a subselect in a larger query, possibly joining on the id field to get more fields from the preferred row, Or ordering the query in a particular way.

这篇关于SQL:如何使用DISTINCT保持行顺序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！