Sort by minimum value of two columns

问题:

I use SQL Server 2008 R2.

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

The table looks like this:

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

I want my data to be sorted by minimal of two dates.

What is the simplest way to sort this table that way?

回答1:

NOT NULL columns. You need to add CASE statement into ORDER BY clause in following:

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

---

NULLABLE columns. As Zohar Peled wrote in comments if columns are nullable you could use ISNULL (but better to use COALESCE instead of ISNULL, because It's ANSI SQL standard) in 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

---

You can read about ANSI standard dateformat 1753-01-01 here.

回答2:

Use a CASE expression in the ORDER BY:

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

Edit:

If null values need to be considered, 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 null here.) Works just like before.

Else use COALESCE() to order by date2 (when date2 is not null), or date1 (when date2 is null), or by null (if both dates are null.)

回答3:

The simplest way is using of the VALUES keyword, like 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 the cases, even with nullable columns.

Edit :

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

• It won't work if the columns are of the Date type (if you use the dates before 01/01/1753)
• It won't work in case one of the columns is NULL. It interprets the NULL value as the minimal datetime value. But is it actually true? It isn't even datetime, it is nothing.
• The IF expression will be much more complicated if we use more than two columns.

According to the question:

What is the simplest way to sort this table that way?

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

• It doesn't take a lot of coding to implement it - simply add an one more line.
• You don't need to care about whether the columns are nullable or not. You just use the code and it works.
• You can extend the number of columns in your query simply by adding the one after a comma.
• It works with the Date columns and you don't need to modify the code.

Edit 2 :

Zohar Peled suggested the following way of order:

I would order the rows by this rules: first, when both null, second, when date1 is null, third, when date 2 is null, fourth, min(date1, date2)

So, for this case the solution can be reached by using of the same approach, like 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 output for this code is below:

The COALESCE solution will not sort the table this way. It messes up the rows where at least one cell of the NULL value. The output of it is the following:

Hope this helps and waiting for critics.

回答4:

This may be an alternate solution which 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 Query

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

回答5:

If you don't want to use Case statement in the Order By , then this is another approach, just 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

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

回答7:

Code for max

I'm using CROSS APPLY, I am not sure about the performance, But CROSS APPLY often has a better 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

回答8:

I'd shift focus from how to do this to why you need this - and propose to change the schema instead. The rule of thumb is: if you need to pull stunts to access your data, there is a bad design decision.

As you've seen, this task is very untypical for SQL so, though it's possible, all the proposed methods are painfully slow in comparison to an ordinary ORDER BY.

• If you need to do this often then the minimum of the two dates must have some independent physical meaning for your application.
  • Which justifies a separate column (or maybe a column replacing one of the two) - maintained by a trigger or even manually if the meaning is independent enough for the column to possibly be neither in some cases.

回答9:

I think when you want to sort on both fields of date1 and date2, you should have both of them in the ORDER BY part, like this:

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

Result can 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 have a prefect result with 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

Results will be like this:

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

回答10:

There's an another option. You can calculate the result column by needed logic and cover the select by external one with ordering by your column. In this case the code will be the following:

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 benefit of this solution is that you can add necessary filtering queries (in the inner select) and still the indexes will be useful.

回答11:

I would order the rows by this rules:

1. when both null
2. when date1 is null
3. when date 2 is null
4. min(date1, date2)

To do this a nested case will be simple and efficient (unless the table is very large) according to this post.

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

回答12:

You can use min function in order by clause:

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

You can also use case statement in order by clause but as you know the result of comparing (> and <) any value (null or none null) with null is not true even if you have setted ansi_nulls to off. so for guaranteeing the sort you wanted, you need to handle nulls, as you know in case clause if the result of a when is true then further when statements 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

Also here are some other solutions if your scenario be different maybe maybe you evaluate the result of comparing multiple columns(or a calculation) inside a separated field and finally order by that calculated field without using any condition inside your order by clause.

回答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 the @dyatchenko answer but without NULL issue