Sort by the minimum value of two columns

Question

I am using SQL Server 2008 R2 .

I need to sort a table by the minimum value of two columns.

The table looks like this:

 ID: integer; Date1: datetime; Date2: datetime. 

I want my data to be sorted with at least two dates.

What is the easiest way to sort this table?

Answer 1

NOT NULL columns . You need to add CASE to ORDER BY in the following:

 SELECT Id, Date1, Date2 FROM YourTable ORDER BY CASE WHEN Date1 < Date2 THEN Date1 ELSE Date2 END 

---

NULLABLE columns Since Zohar Peled wrote in the comments if the columns are null, you can use ISNULL (but it is better to use COALESCE instead of ISNULL because It is ANSI SQL standard ) in the following:

 SELECT Id, Date1, Date2 FROM YourTable ORDER BY CASE WHEN COALESCE(Date1, '1753-01-01') < COALESCE(Date2, '1753-01-01') THEN Date1 ELSE Date2 END 

---

Here you can read information about the standard ANSI 1753-01-01 format here .

Answer 2

Use the CASE expression in ORDER BY :

  ORDER BY case when date1 < date2 then date1 else date2 end 

Edit:

If you need to consider null values, add coalesce() :

  ORDER BY case when date1 < date2 then date1 else coalesce(date2,date1) end 

Explanation:

If date1 <date2, then order by date1. (Both dates are non-zero here.) Works the same as before.

Otherwise, use COALESCE() to sort by date2 (when date2 is not zero), or by date1 (when date2 is zero), or zero (if both dates are zero.)

Answer 3

The easiest way is to use the VALUES keyword, for example the following:

 SELECT ID, Date1, Date2 FROM YourTable ORDER BY (SELECT MIN(v) FROM (VALUES (Date1), (Date2)) AS value(v)) 

This code will work for all cases, even with columns with zero .

Edit:

The solution with the keyword COALESCE not universal. It has important limitations:

• It will not work if the columns are of type Date (if you use dates before 01/01/1753 )
• It will not work if one of the columns is NULL . It interprets the NULL value as the minimum datetime value. But is this really true? It is not even datetime , it is nothing.
• An IF expression will be much more complicated if we use more than two columns.

According to the question:

What is the easiest way to sort this table?

The shortest and simplest solution is the one described above, because:

• It does not require a lot of coding to implement it - just add another line.
• You do not need to worry about whether the columns are nullable or not. You just use the code and it works.
• You can expand the number of columns in a query by simply adding it after the decimal point.
• It works with Date columns and you do not need to change the code.

Edit 2:

The Zohar Peled proposed the following order:

I would order strings according to these rules: first, when both null, second, when date1 is null, third when date 2 is null, fourth, min (date1, date2)

So, for this case, a solution can be achieved using the same approach as the following:

 SELECT ID, Date1, Date2 FROM YourTable ORDER BY CASE WHEN Date1 IS NULL AND Date2 IS NULL THEN 0 WHEN Date1 IS NULL THEN 1 WHEN Date2 IS NULL THEN 2 ELSE 3 END, (SELECT MIN(v) FROM (VALUES ([Date1]), ([Date2])) AS value(v)) 

The result for this code is below:

COALESCE solution will not sort the table this way. It puts the rows where at least one cell is NULL . His conclusion is as follows:

Hope this helps and awaits critics.

Answer 4

If you don't want to use the Case statement in Order By , this is a different approach by simply moving the Case statement to Select

 SELECT Id, Date1, Date2 FROM (SELECT Id, Date1, Date2 ,CASE WHEN Date1 < Date2 THEN Date1 ELSE Date2 END as MinDate FROM YourTable) as T ORDER BY MinDate 

Answer 5

This may be an alternative solution that does not require branching like CASE WHEN . This is based on the formula max(a,b)=1/2(a+b+|a−b|) , as described here . We get the absolute values ​​of a and b using DATEDIFF with a reference date ( '1773-01-01' ).

 ORDER BY (DATEDIFF(d,'17730101' ,isnull(Startdate,enddate)) + DATEDIFF(d,'17730101' ,isnull(EndDate,Startdate)) - ABS(DATEDIFF(d,isnull(Startdate,enddate),isnull(EndDate,Startdate)))) 

Test Data

 Create Table #DateData(ID int Identity, Name varchar(15),Startdate datetime,EndDate DateTime) Insert Into #DateData(Name,Startdate,EndDate) values ('myName','2015-04-17 18:48:27','2015-04-18 18:48:27') Insert Into #DateData(Name,Startdate,EndDate) values ('myName','2015-04-19 18:48:27','2015-04-18 18:48:27') Insert Into #DateData(Name,Startdate,EndDate) values ('myName','2015-04-20 18:48:27','2015-04-18 18:48:27') Insert Into #DateData(Name,Startdate,EndDate) values ('myName','2015-04-11 18:48:27','2015-04-22 18:48:27') Insert Into #DateData(Name,Startdate,EndDate) values ('myName','2015-05-09 18:48:27','2015-04-18 18:48:27') Insert Into #DateData(Name,Startdate,EndDate) values ('myName','2015-04-17 19:07:38','2015-04-17 18:55:38') Insert Into #DateData(Name,Startdate,EndDate) values ('myName','2015-04-17 19:07:38','2015-05-12 18:56:29') 

Complete request

 select * from #DateData order by (DATEDIFF(d,'17730101' ,isnull(Startdate,enddate)) + DATEDIFF(d,'17730101' ,isnull(EndDate,Startdate)) - ABS(DATEDIFF(d,isnull(Startdate,enddate),isnull(EndDate,Startdate)))) 

Answer 6

I prefer this way to handle nullable columns:

 SELECT Id, Date1, Date2 FROM YourTable ORDER BY CASE WHEN Date1 < Date2 OR Date1 IS NULL THEN Date1 ELSE Date2 END 

Answer 7

Code for max

I use CROSS APPLY , I'm not sure about the performance, but CROSS APPLY often has the best performance in my experience.

 CREATE TABLE #Test (ID INT, Date1 DATETIME, Date2 DATETIME) INSERT INTO #Test SELECT 1, NULL, '1/1/1';INSERT INTO #Test SELECT 2, NULL, NULL;INSERT INTO #Test SELECT 3, '2/2/2', '3/3/1';INSERT INTO #Test SELECT 4, '3/3/3', '11/1/1' SELECT t.ID, Date1, Date2, MinDate FROM #TEST t CROSS APPLY (SELECT MIN(d) MinDate FROM (VALUES (Date1), (Date2)) AS a(d)) md ORDER BY MinDate DROP TABLE #Test 

Answer 8

I think that if you want to sort date1 and date2 by both fields, you should have both of them in the ORDER BY part, for example:

 SELECT * FROM aTable ORDER BY CASE WHEN date1 < date2 THEN date1 ELSE date2 END, CASE WHEN date1 < date2 THEN date2 ELSE date1 END 

The result could be like this:

 date1 | date2 -----------+------------ 2015-04-25 | 2015-04-21 2015-04-26 | 2015-04-21 2015-04-25 | 2015-04-22 2015-04-22 | 2015-04-26 

---

To get the prefect result using Null values, use:

 SELECT * FROM aTable ORDER BY CASE WHEN date1 IS NULL THEN NULL WHEN date1 < date2 THEN date1 ELSE date2 END ,CASE WHEN date2 IS NULL THEN date1 WHEN date1 IS NULL THEN date2 WHEN date1 < date2 THEN date2 ELSE date1 END 

The results will be as follows:

 date1 | date2 -----------+------------ NULL | NULL NULL | 2015-04-22 2015-04-26 | NULL 2015-04-25 | 2015-04-21 2015-04-26 | 2015-04-21 2015-04-25 | 2015-04-22 

Answer 9

I would focus on how to do this, and why you need it, and instead propose changing the scheme. A rule of thumb: if you need to pull tricks to access your data, there is a poor design decision.

As you saw, this task is not very typical for SQL, therefore, although it is possible, all the proposed methods are painfully slow compared to the usual ORDER BY .

• If you need to do this often, then a minimum of two dates should have some physical meaning for your application.
  • Which justifies a separate column (or maybe a column replacing one of the two) supported by a trigger or even manually if the value is independent enough so that the column is not in any case.

Answer 10

I would order strings according to these rules:

• when both are null
• when date1 is null
• when date 2 is zero
• min (date1, date2)

To do this, the nested case will be simple and effective (if the table is not very large) in accordance with this message .

 SELECT ID, Date1, Date2 FROM YourTable ORDER BY CASE WHEN Date1 IS NULL AND Date2 IS NULL THEN 0 WHEN Date1 IS NULL THEN 1 WHEN Date2 IS NULL THEN 2 ELSE 3 END, CASE WHEN Date1 < Date2 THEN Date1 ELSE Date2 END 

Answer 11

There is one more option. You can calculate the column of results by the necessary logic and cover the selection by the external one by ordering by column. In this case, the code will look like this:

 select ID, x.Date1, x.Date2 from ( select ID, Date1, Date2, SortColumn = case when Date1 < Date2 then Date1 else Date2 end from YourTable ) x order by x.SortColumn 

The advantage of this solution is that you can add the necessary filtering requests (in the internal selection), and yet the indexes will be useful.

Answer 12

You can use the min function in order by :

 select * from [table] d order by ( select min(qt) from ( select d.date1 t union select d.date2) q ) 

You can also use the case operator in order by , but as you know the result of the comparison ( > and < ), any value (null or none null) with a null value is not true , even if you set ansi_nulls to off . therefore, to guarantee the type you need, you need to handle null s, as you know in case , if the result of when is true , then further when expressions are not evaluated, so you can say

 select * from [table] order by case when date1 is null then date2 when date2 is null then date1 when date1<date2 then date1 -- surely date1 and date2 are not null here else date2 end 

There are also several other solutions, if your scenario is different from another, maybe you can evaluate the result of comparing several columns (or calculations) inside a divided field and finally order this calculated field without using any condition in your order by clause.

Answer 13

 SELECT ID, Date1, Date2 FROM YourTable ORDER BY (SELECT TOP(1) v FROM (VALUES (Date1), (Date2)) AS value(v) ORDER BY v) 

Very similar to @dyatchenko answer, but without NULL issue