Use order by with two columns

Question

I have two datetime type column and I want to use order by for both column, if anyone is have recent date then it should come on top

I tried the below but it is returning below result which is not as expected.

select  *
from v_ServiceHistory
where r_customer = 80
order by r_closed desc, sfh_regeneratedon desc

Answer 1

Use a case expression to get the most recent:

order by case when r_closed > sfh_regeneratedon then r_closed 
              else coalesce(sfh_regeneratedon,  r_closed)
         end desc

Explanation:

1. If both columns are non-null, and r_closed > sfh_regeneratedon, then order by r_closed.

2. Otherwise, order by sfh_regeneratedon if not null, or (if sfh_regeneratedon is null) order by r_closed.

Recent SQL Server has GREATEST:

order by case GREATEST(col1, col2, col3 ) desc

(SQL Server handles NULL's like 'If one or more arguments aren't NULL, then NULL arguments are ignored during comparison. If all arguments are NULL, then GREATEST returns NULL.' - which isn't according to the ANSI/ISO SQL standard.)