Sort empty last - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sort empty last

Author

Topic

ajmufic
Starting Member

17 Posts

Posted - 2007-11-14 : 03:39:09

Is there any way to sort your results in a way that the empty results comes last? When I sort my recordset on a certain column I first get the empty values and the A, B, C etc. I would like to have it A, B, C, ..., Empty ones.

Regards
Ajmufic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-14 : 03:43:03

ORDER BY
CASE
WHEN Col1 = '' THEN 1 -- Empty
WHEN Col1 IS NULL THEN 2 -- NULL
ELSE 0
END, Col1

E 12°55'05.25"
N 56°04'39.16"

ajmufic
Starting Member

17 Posts

Posted - 2007-11-14 : 04:05:54

Thanks Peso. Works great.

EXCEPT when I sort descending... If I sort it ascending the empty ones comes last but when I sort them ascending they still comes last...? Shouldn't they technically come first (that's also what I want), or am I thinking wrong?

I also noticed that for numbers it put 0 (zero) after all other numbers. Example: 1, 2, 3, 4, 5, 7, 0, Null

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-14 : 04:14:49

That's because empty space '' is treated and converted as ZERO for numeric columns.
Remove = '' for numeric columns.

-- Ascending
ORDER BY
CASE
WHEN Col1 IS NULL THEN 1 -- NULL
ELSE 0
END, Col1

-- Descending
ORDER BY
CASE
WHEN Col1 IS NULL THEN 1 -- NULL
ELSE 0
END, Col1 DESC

E 12°55'05.25"
N 56°04'39.16"

ajmufic
Starting Member

17 Posts

Posted - 2007-11-14 : 04:37:32

Ok, I see, thanks.

Do you know how I should solve the ascending/descending problem?
HOW IT IS NOW
Ascending: A, ..., Z, Empty
Descending: Z, ..., A, Empty

HOW I WANT IT
Ascending: A, ..., Z, Empty
Descending: Empty, Z, ..., A

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-14 : 04:58:21

1 order by case when col is null then 1 end, col
2 order by case when col is null then 0 else 1 end, col desc

Madhivanan

Failing to plan is Planning to fail

ajmufic
Starting Member

17 Posts

Posted - 2007-11-14 : 05:17:31

Perfect!
Thanks guys!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources