SQLServerORDERBY 日期和空值最后

我想按日期订货。我希望最近的约会能排在第一位。这很简单，但是有许多记录是空的，并且这些记录出现在任何有日期的记录之前。

我尝试了一些方法，但没有成功:

ORDER BY ISNULL(Next_Contact_Date, 0)


ORDER BY ISNULL(Next_Contact_Date, 999999999)


ORDER BY coalesce(Next_Contact_Date, 99/99/9999)

我怎样才能按日期排序，最后是空数? 数据类型是 smalldatetime。

117829 次浏览
小开
最佳答案

smalldatetime has range up to June 6, 2079 so you can use

ORDER BY ISNULL(Next_Contact_Date, '2079-06-05T23:59:00')

If no legitimate records will have that date.

If this is not an assumption you fancy relying on a more robust option is sorting on two columns. 

ORDER BY CASE WHEN Next_Contact_Date IS NULL THEN 1 ELSE 0 END, Next_Contact_Date

Both of the above suggestions are not able to use an index to avoid a sort however and give similar looking plans.

enter image description here

One other possibility if such an index exists is

SELECT 1 AS Grp, Next_Contact_Date
FROM T
WHERE Next_Contact_Date IS NOT NULL
UNION ALL
SELECT 2 AS Grp, Next_Contact_Date
FROM T
WHERE Next_Contact_Date IS NULL
ORDER BY Grp, Next_Contact_Date

Plan

小开

If your SQL doesn't support NULLS FIRST or NULLS LAST, the simplest way to do this is to use the value IS NULL expression:

ORDER BY Next_Contact_Date IS NULL, Next_Contact_Date

to put the nulls at the end (NULLS LAST) or 

ORDER BY Next_Contact_Date IS NOT NULL, Next_Contact_Date

to put the nulls at the front. This doesn't require knowing the type of the column and is easier to read than the CASE expression.

EDIT: Alas, while this works in other SQL implementations like PostgreSQL and MySQL, it doesn't work in MS SQL Server. I didn't have a SQL Server to test against and relied on Microsoft's documentation and testing with other SQL implementations. According to Microsoft, value IS NULL is an expression that should be usable just like any other expression. And ORDER BY is supposed to take expressions just like any other statement that takes an expression. But it doesn't actually work.

The best solution for SQL Server therefore appears to be the CASE expression.

小开

A bit late, but maybe someone finds it useful.

For me, ISNULL was out of question due to the table scan. UNION ALL would need me to repeat a complex query, and due to me selecting only the TOP X it would not have been very efficient.

If you are able to change the table design, you can:

  1. Add another field, just for sorting, such as Next_Contact_Date_Sort.

  2. Create a trigger that fills that field with a large (or small) value, depending on what you need:

    CREATE TRIGGER FILL_SORTABLE_DATE ON YOUR_TABLE AFTER INSERT,UPDATE AS
BEGIN
SET NOCOUNT ON;
IF (update(Next_Contact_Date)) BEGIN
UPDATE YOUR_TABLE SET Next_Contact_Date_Sort=IIF(YOUR_TABLE.Next_Contact_Date IS NULL, 99/99/9999, YOUR_TABLE.Next_Contact_Date_Sort) FROM inserted i WHERE YOUR_TABLE.key1=i.key1 AND YOUR_TABLE.key2=i.key2
END
END
小开

Use desc and multiply by -1 if necessary. Example for ascending int ordering with nulls last:

select *
from
(select null v union all select 1 v union all select 2 v) t
order by -t.v desc
小开 
order by -cast([Next_Contact_Date] as bigint) desc
小开

According to Itzik Ben-Gan, author of T-SQL Fundamentals for MS SQL Server 2012, "By default, SQL Server sorts NULL marks before non-NULL values. To get NULL marks to sort last, you can use a CASE expression that returns 1 when the" Next_Contact_Date column is NULL, "and 0 when it is not NULL. Non-NULL marks get 0 back from the expression; therefore, they sort before NULL marks (which get 1). This CASE expression is used as the first sort column." The Next_Contact_Date column "should be specified as the second sort column. This way, non-NULL marks sort correctly among themselves." Here is the solution query for your example for MS SQL Server 2012 (and SQL Server 2014): 

ORDER BY
CASE
WHEN Next_Contact_Date IS NULL THEN 1
ELSE 0
END, Next_Contact_Date;

Equivalent code using IIF syntax:

ORDER BY
IIF(Next_Contact_Date IS NULL, 1, 0),
Next_Contact_Date;
小开

I know this is old but this is what worked for me

Order by Isnull(Date,'12/31/9999')
小开

I think I found a way to show nulls in the end and still be able to use indexes for sorting.

The idea is super simple - create a calculatable column which will be based on existing column, and put an index on it.

ALTER TABLE dbo.Users
ADD [FirstNameNullLast]
AS (case when [FirstName] IS NOT NULL AND (ltrim(rtrim([FirstName]))<>N'' OR [FirstName] IS NULL) then [FirstName] else N'ZZZZZZZZZZ' end) PERSISTED

So, we are creating a persisted calculatable column in the SQL, in that column all blank and null values will be replaced by 'ZZZZZZZZ', this will mean, that if we will try to sort based on that column, we will see all the null or blank values in the end. Now we can use it in our new index.

Like this:

CREATE NONCLUSTERED INDEX [IX_Users_FirstNameNullLast] ON [dbo].[Users]
(
[FirstNameNullLast] ASC
)

So, this is an ordinary nonclustered index. We can change it however we want, i.e. include extra columns, increase number of indexes columns, change sorting order etc.

小开

I know this is a old thread, but in SQL Server nulls are always lower than non-null values. So it's only necessary to order by Desc

In your case Order by Next_Contact_Date Desc should be enough.

Source: order by with nulls- LearnSql


提交答案