CODEWITHSUNDEEP
sqlsql-server
juergen d
juergen d

Reputation: 204746

ORDER BY different columns in same priority

I have a table with 2 date columns:

create_date and modify_date.
create_date is always set and modify_date sometimes.

Now I want to order my query by both of them with the same priority.

If I do this:

order by create_date desc, modify_date desc

I first get all rows sorted by create_date and then by modify_date but I want the rows sorted by both in the same priority. I need this:

create_date  |  modify_date
2011-12-31   |  -
2011-12-01   |  2011-12-30
2011-12-29   |  -

Upvotes: 2

Views: 757

Answers (3)

okrumnow
okrumnow

Reputation: 2416

The column naming implies that modify_date is either not set or higher then creation date, so the other answers will work. If not, you may use a user defined function as described here (adjust to use Dates instead of int). Then use

ORDER BY dbo.HigherArgument(create_date, modify_date) DESC

Upvotes: 0

Curtis
Curtis

Reputation: 103348

Use ISNULL() to order by modify_date, and if modify_date is NULL, it will use the create_date value:

ORDER BY ISNULL(modify_date, create_date) DESC

Upvotes: 3

Andrew
Andrew

Reputation: 27294

select *
from yourtable
order by coalesce(modify_date, create_date) desc

Upvotes: 5

Related Questions