SQL Select Distinct Values, but order by a different value

Question

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!

Marc Gravell · Accepted Answer · 2009-11-23 19:50:55Z

23

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

answered Nov 23, 2009 at 19:50

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Erebus Over a year ago

As a follow up, what if I wanted to select all values in the table, not just orderId and datetime? I have a feeling that's a totally separate questions, but...

Marc Gravell Over a year ago

You would need to either aggregate all the columns, or select a particular row (min(id), max(id), or similar) and do a join / sub-query.

unclepaul84 · Accepted Answer · 2009-11-23 19:54:21Z

3

Perhaps a CTE would help:

WITH CTE
AS
(

SELECT orderId FROM table ORDER BY datetime DESC

)

SELECT DISTINCT orderId  FROM CTE

answered Nov 23, 2009 at 19:54

unclepaul84

1,4048 silver badges16 bronze badges

3 Comments

unclepaul84 Over a year ago

Thanks for the correction, for a moment i forgot that this is not the MSDN SQL forum :)

Atomosk Over a year ago

Says "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.".

Lukas Eder Over a year ago

Does SQL Server guarantee the ordering of the CTE to be maintained? Seems like a very risky internal thing to rely on, if it even works. Especially since there are formally correct solutions, that guarantee the desired outcome.

jank · Accepted Answer · 2014-06-27 11:33:56Z

0

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

That worked for me.

answered Jun 27, 2014 at 11:33

jank

11

1 Comment

Lukas Eder Over a year ago

This is incorrect. The database is free to ignore the derived table's ORDER BY clause, which it will effectively do when DISTINCT is implemented using hashing, e.g. in a more complex query. Your concrete database optimiser implementation may have accidentally produced the desired result, but you must not rely on it!

Collectives™ on Stack Overflow

SQL Select Distinct Values, but order by a different value

3 Answers 3

2 Comments

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related