SQL选择“不同的值”,但按不同的值排序

时间:2022-09-16 23:46:25

I want to select all distinct order_ids from my table, and order that list by the date column. Using DISTINCT is of course a query-wide parameter, so trying something like this doesn't work:

我想从我的表中选择所有不同的order_id,并按日期列排序该列表。使用DISTINCT当然是一个查询范围的参数,所以尝试这样的东西不起作用:

SELECT DISTINCT(orderId, datetime) 
FROM table 
ORDER BY datetime DESC

This returns all DISTINCT combinations of the orderId and datetime, so I'm left with multiple orderIds, which I don't want. Therefore I'm thinking that the DISTINCT clause is not the way to go. Does anyone have any suggestions on how I could solve this problem?

这将返回orderId和datetime的所有DISTINCT组合,因此我留下了多个orderIds,这是我不想要的。因此,我认为DISTINCT条款不是要走的路。有没有人对如何解决这个问题有任何建议?

Thanks!

谢谢!

3 个解决方案

#1


21  

If there are multiple rows for the order, which date do you want to show? perhaps:

如果订单有多行,您要显示哪个日期?也许:

SELECT [orderId], MAX([datetime])
FROM [table]
GROUP BY [orderId]
ORDER BY MAX([datetime]) DESC

#2


3  

Perhaps a CTE would help:

也许CTE会有所帮助:

WITH CTE
AS
(

SELECT orderId FROM table ORDER BY datetime DESC

)

SELECT DISTINCT orderId  FROM CTE

#3


-1  

SELECT DISTINCT * FROM 
  (SELECT value1 
   FROM table1 
   ORDER BY value2);

That worked for me.

这对我有用。

#1


21  

If there are multiple rows for the order, which date do you want to show? perhaps:

如果订单有多行,您要显示哪个日期?也许:

SELECT [orderId], MAX([datetime])
FROM [table]
GROUP BY [orderId]
ORDER BY MAX([datetime]) DESC

#2


3  

Perhaps a CTE would help:

也许CTE会有所帮助:

WITH CTE
AS
(

SELECT orderId FROM table ORDER BY datetime DESC

)

SELECT DISTINCT orderId  FROM CTE

#3


-1  

SELECT DISTINCT * FROM 
  (SELECT value1 
   FROM table1 
   ORDER BY value2);

That worked for me.

这对我有用。