SQL Server ORDER BY date и nulls last

Я пытаюсь упорядочить по дате. Я хочу, чтобы самые последние даты были первыми. Это достаточно легко, но есть много записей, которые являются нулевыми, и они поступают перед любыми записями, которые имеют дату.

Я пробовал несколько вещей без успеха:

ORDER BY ISNULL(Next_Contact_Date, 0)

ORDER BY ISNULL(Next_Contact_Date, 999999999)

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

Как я могу упорядочить по дате и иметь нулевые значения? Тип данных smalldatetime.

Ответ 1

smalldatetime имеет диапазон до 6 июня 2079 года, поэтому вы можете использовать

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

Если никакие законные записи не будут иметь эту дату.

Если это не предположение, вы полагаете, что полагаетесь на более надежный вариант, сортировка выполняется по двум столбцам.

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

Оба вышеупомянутых предложения не могут использовать индекс, чтобы избежать сортировки, и дать похожие планы.

Другая возможность, если такой индекс существует, -

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

Ответ 2

По словам Ицика Бен-Гана, автора основ T-SQL для MS SQL Server 2012, "По умолчанию SQL Server сортирует метки NULL до значений NULL Чтобы получить NULL метки для сортировки в последний раз, вы можете использовать выражение CASE, которое возвращает 1, когда столбец Next_Contact_Date NULL," и 0, когда он не является NULL. Знаки <- > NULL возвращаются обратно из выражения, поэтому они сортируются до NULL. > метки (которые получают 1). Это выражение CASE используется как первый столбец сортировки. "Столбец Next_Contact_Date должен быть указан как второй столбец сортировки. Таким образом, метки NULL сортируются правильно между собой." Вот пример решения для вашего примера для MS SQL Server 2012 (и SQL Server 2014):

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

Эквивалентный код с использованием синтаксиса IIF:

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

Ответ 3

Немного поздно, но, возможно, кто-то сочтет это полезным.

Для меня ISNULL не могло быть и речи из-за сканирования таблицы. UNION ALL мне понадобится повторить сложный запрос, и из-за того, что я выбираю только TOP X, он не был бы очень эффективным.

Если вы можете изменить дизайн таблицы, вы можете:

Добавьте еще одно поле, просто для сортировки, например Next_Contact_Date_Sort.

Создайте триггер, который заполняет это поле большим (или маленьким) значением в зависимости от того, что вам нужно:

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

Ответ 4

order by -cast([Next_Contact_Date] as bigint) desc

Ответ 5

Стандартный способ ANSI:

ORDER BY Next_Contact_Date ASC NULLS LAST

Обратите внимание, что также существует противоположный NULLS FIRST.

Ответ 6

Если ваш SQL не поддерживает NULLS FIRST или NULLS LAST, самый простой способ сделать это - использовать выражение value IS NULL:

ORDER BY Next_Contact_Date IS NULL, Next_Contact_Date

чтобы положить нули в конец (NULLS LAST) или

ORDER BY Next_Contact_Date IS NOT NULL, Next_Contact_Date

поставить нули на фронте. Это не требует знания типа столбца и его легче читать, чем выражение CASE.

EDIT: Увы, в то время как это работает в других реализациях SQL, таких как PostgreSQL и MySQL, оно не работает в MS SQL Server. У меня не было SQL Server для тестирования и использования документации Microsoft и тестирования с другими реализациями SQL. Согласно Microsoft, value IS NULL - это выражение, которое должно использоваться как любое другое выражение. И ORDER BY должен принимать выражения точно так же, как и любой другой оператор, который принимает выражение. Но на самом деле это не работает.

Лучшим решением для SQL Server является выражение CASE.

Ответ 7

Используйте desc и умножьте на -1, если это необходимо. Пример для восходящего int с нулевым значением:

select * 
from
(select null v union all select 1 v union all select 2 v) t
order by -t.v desc