ORDER BY Multiple Columns

[robulator]

I have a program that constructs a query based on user input that includes several datetime fields (this in SQL Server). I would like to sort the results based on the most recent date of all of the date fields (some of the entries may be Null). You can only use one field in the Max function so that doesn't work. Does anyone have any suggestions?

Thanks!

Rob

 

[rac2]

Here's an idea. Create a view with a column that shows the latest date in a row for all of the date columns in your base table.

SELECT *,
CASE
   WHEN date_x > date_y AND  date_x > date_z
      THEN date_x
   WHEN date_y > date_x AND  date_y > date_z
      THEN date_y
   WHEN date_z > date_x AND  date_z > date_y
      THEN date_z
   .
   .
   .
END AS date_last_activity
FROM steps_completed

Maybe put ISNULL functions in there to handle the null values. Put in an ELSE clause if you want to see rows with all nulls in the date columns. You may need to choose a very early date which means the same thing as no date for the ISNULL and ELSE.

The view could be tailored to the query you described, or it could be written to return the primary key, other keys, and the date_last_activity. Then it could be joined with various tables for various uses.

 

[robulator]

Thanks! That does it.

 

[angiole]

Another approach, if SQL server supports the function GREATEST, is:

Select   GREATEST(in_Date1,In_Date2,In_Date3 ...) In_Date,
         *
From     Table1
Where    ...
Order by 1 Desc

AA 8~)

 

[robulator]

Unfortunately, SQL Server doesn't support the GREATEST function or anything similar, as far as I know.