asked on

NULL value in SQL Server

Please advise how to display NULL value as a first record in the query output.

Ex; select distinct (Name) from Table_Name

Output:

Name
====
A
B
C
D
NULL
E
F

I want output like

Name
====
NULL
A
B
C
D
E
F

ASKER CERTIFIED SOLUTION

Dale Burrell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

ruengyot

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Horn

select distinct (Name)
from Table_Name
order by CASE WHEN ISNULL(Name) THEN 1 ELSE 2 END, Name

Qlemo

If you need a particular order, never rely on the "automatic" order of a select. That is, always apply a ORDER BY.
NULL is ordered differently by different DBMS, some sort NULL as highest possible value, others as lowest. To make sure it works as expected, use a combined sort:
order by isnull(name) desc, name

PortletPaul

Aren't you assuming there will be a NULL returned?

i.e. What happens to your logic if it does not get returned?

(looks like you are trying to prepare a drop-down list where the order is important and a NULL is the "default" or "all")

sqldba2013

ASKER

Qlemo

Sorry for the wrong syntax in http:#a39955357, I was mislead by a prior post.
The first two posts should work fine for MSSQL, and Jim Horn's SQL should be

select distinct (Name) 
from Table_Name 
order by CASE WHEN Name IS NULL THEN 1 ELSE 2 END, Name

Open in new window

sqldba2013

ASKER

sqldba2013

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for sqldba2013's comment #a39991646
Assisted answer: 42 points for dale_burrell's comment #a39954993
Assisted answer: 42 points for ruengyot's comment #a39955002
Assisted answer: 42 points for jimhorn's comment #a39955035
Assisted answer: 42 points for Qlemo's comment #a39955357
Assisted answer: 41 points for PortletPaul's comment #a39955569
Assisted answer: 41 points for Qlemo's comment #a39965925

for the following reason:

--

sqldba2013

ASKER

sqldba2013

ASKER

question has been answered