按两列的最小值排序

I use SQL Server 2008 R2.

我使用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?

对这张桌子进行排序的最简单的方法是什么?

13 个解决方案

#1

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

NOT NULL列。您需要在以下条款中按顺序添加CASE statement:

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:

可以为空的列。正如Zohar Peled在评论中所写的，如果列是空的，您可以使用ISNULL(但是最好使用合并而不是ISNULL，因为它是ANSI SQL标准):

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.

您可以在这里阅读ANSI标准dateformat 1753-01-01。

#2

Use a CASE in the ORDER BY:

按下列次序使用个案:

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

#3

The simplest way is using of the VALUES keyword, like the following:

最简单的方法是使用VALUES关键字，如下所示:

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:

使用COALESCE关键字的解决方案并不是通用的。它有重要的限制:

• It won't work if the columns are of the Date type (if you use the dates before 01/01/1753)
• 如果列是日期类型(如果您使用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.
• 如果其中一个列是空的，它就无法工作。它将空值解释为最小的datetime值。但这是真的吗?它不是datetime，也不是什么。
• The IF expression will be much more complicated if we use more than two columns.
• 如果我们使用超过两列的话，IF表达式将会更加复杂。

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 :

编辑2:

Zohar Peled suggested the following way of order:

Zohar Peled提出了以下的顺序:

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)

我将按照以下规则对行进行排序:第一，当两个都为null时，第二，当date1为null时，第三，当日期2为null时，第四，最小(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').

这可能是另一种解决方案，在这种情况下不需要分支。这是基于公式马克斯(a,b)= 1/2(a + b + b−| |)这里描述。我们使用DATEDIFF和一个参考日期(“1773-01-01”)获取a和b的绝对值。

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

如果不希望按Order By使用Case语句，那么这是另一种方法，只需将Case语句移动到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.

我使用的是CROSS APPLY，我对性能不是很确定，但是CROSS APPLY在我的经验中常常有更好的性能。

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.

正如您所看到的，这个任务对于SQL是非常不典型的，因此，尽管有可能，但是与普通的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:

我认为当你想对date1和date2的两个字段进行排序时，你应该将它们按部分排序，如下所示:

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. 当两个零
3. when date1 is null
4. 当date1零
5. when date 2 is null
6. 当日期2为空时
7. min(date1, date2)
8. 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:

你可以按顺序使用min函数:

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:

您还可以使用case语句在order by子句中,但如你所知的结果比较(>和<)任何值(null或空没有),零是不正确的,即使你有设置ansi_nulls放假所以对于保证你想要的,你需要处理null,如你所知,以防条款如果是真的,那么进一步的结果当语句不评价你可以说:

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.

这里还有一些其他的解决方案，如果你的场景不同，也许你可以评估在一个单独的字段中比较多个列(或一个计算)的结果，最后根据计算字段排序，而不使用order by子句中的任何条件。

#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

非常类似于@dyatchenko的回答，但是没有无效的问题