Sorting with null - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Sorting with null

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-02-03 : 08:53:12

Francis writes "I would like to sort a column but I would like all the fields with null values to be at the bottom whether the sorting is ascending or descending. How can I achieve this in a stored procedure or SQL statement? I am using MS SQL 7. Thanks."

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-03 : 09:37:49

ORDER BY ISNULL(Column, (maximum possible value to make it sort at the end))

for example:

SELECT * FROM
YourTable
ORDER BY ISNULL(CustomerName, 'ZZZZZZ')

or

ORDER BY ISNULL(CustomerID, 99999999)

- Jeff

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-02-03 : 13:11:23

This may perform a little faster if there is an index on the column being sorted:

SELECT * FROM YourTable
ORDER BY CASE WHEN CustomerName IS NULL THEN 1 ELSE 0 END,
CustomerName

You can also modify that to sort CustomerName ascending or descending, without affecting the nulls sorting at the bottom.

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-03 : 14:05:00

How About:

SELECT * From (
SELECT TOP 100 PERCENT * FROM YourTable
WHERE CustomerName Is Not Null
Order by CustomerName) As XXX
UNION ALL
SELECT * FROM YourTable
WHERE CustomerName Is Null

What does anyone think? I' appreciate anyone's feedback.

Brett

8-)

I did this on a table I have...

SELECT Count(*) From Ledger_Detail
--Returns: 404218

SELECT * FROM (
SELECT TOP 100 PERCENT * FROM Ledger_Detail
WHERE Tax_Year Is Not Null
Order by Tax_Year) As XXX
UNION ALL
SELECT * FROM Ledger_Detail
WHERE Tax_Year Is Null

Produces:

StmtText
-----------------------------------------------------------------------------------------------------------------------------------------------
|--Concatenation
|--Clustered Index Scan(OBJECT:([TaxReconDB_Prod].[dbo].[Ledger_Detail].[XIE1Ledger_Detail]), WHERE:([Ledger_Detail].[Tax_Year]<>NULL))
|--Clustered Index Scan(OBJECT:([TaxReconDB_Prod].[dbo].[Ledger_Detail].[XIE1Ledger_Detail]), WHERE:([Ledger_Detail].[Tax_Year]=NULL))

(3 row(s) affected)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources