CODEWITHSUNDEEP
sqldistinct
Erebus
Erebus

Reputation: 2038

SQL Select Distinct Values, but order by a different value

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:

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?

Thanks!

Upvotes: 13

Views: 27680

Answers (3)

jank
jank

Reputation: 11

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

That worked for me.

Upvotes: 0

unclepaul84
unclepaul84

Reputation: 1404

Perhaps a CTE would help:

WITH CTE
AS
(

SELECT orderId FROM table ORDER BY datetime DESC

)

SELECT DISTINCT orderId  FROM CTE

Upvotes: 3

Marc Gravell
Marc Gravell

Reputation: 1064244

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

Upvotes: 22

Related Questions