ORDER BY different columns in same priority

Question

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   |  -

score 5 · Accepted Answer · answered Jan 30 '12 at 13:18

5

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

answered Jan 30 '12 at 13:18

Andrew

25,754
4
62
85

score 3 · Answer 2 · answered Jan 30 '12 at 13:19

3

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

answered Jan 30 '12 at 13:19

Curtis

98,395
62
265
345

score 0 · Answer 3 · edited May 23 '17 at 10:34

0

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

edited May 23 '17 at 10:34

Community

1
1

answered Jan 30 '12 at 13:26

okrumnow

2,286
24
38

ORDER BY different columns in same priority

3 Answers3