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?
NOT NULL columns. You need to add CASE expression 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.
CASE WHEN ISNULL(Date1, '01/01/1753') < ISNULL(Date2, '01/01/1753') THEN Date1 ELSE Date2 END –
Aleksandrovsk ORDER BY date1, date2 It will order first by date1 and if 2 or more values of date1 will have the same date then will order by date2. This question is about: how to order by minimum value of 2 columns. It should check which one is lower date1 or date2 and then order by It. –
Azucenaazure Date1 is NULL and Date2 isn't. You take Date1 as the minimal date when it equals to NULL, but it isn't actually true - the minimal date in the row is Date2, because another column doesn't contain datetime value. –
Extravert COALESCE if date1 is null It take 01/01/1753 value. –
Azucenaazure 01/01/1753? Why don't you use the maximum datetime value? And why do you use the NULL value as the date time value? What if we use a date below 01/01/1753? –
Extravert 1753-01-01 –
Azucenaazure COALESCE your solution it messes up the output. –
Extravert 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.)
Edit 2:
If you're using SQL Server 2022 (or later), you can simply use the LEAST() function:
ORDER BY LEAST(date1, date2)
(SQL Server's LEAST() implementation will return the non-null value if the other value is null. ISO/ANSI SQL's LEAST() returns null if any of the arguments is null.)
2015-01-10, 2015-01-20; 2015-01-20, 2015-01-11; 2015-01-15, 2015-01-15. This is the order that you'll get when you sort by MIN of two dates. If you simply order by date1, date2, you'll get different result. –
Qualify 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:
Date type (if you use the dates before 01/01/1753)NULL. It interprets the
NULL value as the minimal datetime value. But is it actually
true? It isn't even datetime, it is nothing. 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:
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.
NULL to the top? If a row has at least one column with the correct value this value will be used in the ORDER BY clause. –
Extravert to order by minimal value in a row which means if we have only one value in a row it will be the minimum. I don't understand why do you think that between NULL and datetime the minimal value is NULL. –
Extravert NULL. –
Extravert COALESCE answer does it in the chaotic order anyway. Just check it. When Date1 is NULL the case is always TRUE - taking the Date1 value (NULL). When Date2 is NULL the case is always FALSE - taking the Date2 value (NULL). In all the cases we are taking the NULL value. –
Extravert 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
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))))
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
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
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
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.
I would order the rows by this rules:
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
Operand type clash: int is incompatible with date –
Extravert Date1 > Date2 is always false in case one of them is NULL. –
Extravert ORDER BY is not the simplest solution for the question. –
Extravert Date2. –
Extravert 2023-04-01 -> 2011-04-01 -> 1750-02-01. Here should be the opposite order. Like this: gyazo.com/c8ef6007a8800b70f8dfbd9e776b3608 –
Extravert 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.
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.
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
© 2022 - 2024 — McMap. All rights reserved.
MINfunction. Please vote: connect.microsoft.com/SQLServer/feedback/details/767183/… – Overtrump