Solved

NULL value in SQL Server

Posted on 2014-03-25
15
357 Views
Last Modified: 2014-04-22
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
0
Comment
Question by:sqldba2013
15 Comments
 
LVL 21

Accepted Solution

by:
Dale Burrell earned 125 total points
ID: 39954993
select distinct (Name) from Table_Name order by isnull(Name, '')
0
 

Assisted Solution

by:ruengyot
ruengyot earned 125 total points
ID: 39955002
select distinct (Name) from Table_Name order by Name
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39955035
select distinct (Name)
from Table_Name
order by CASE WHEN ISNULL(Name) THEN 1 ELSE 2 END, Name
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39955357
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
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39955569
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")
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:sqldba2013
ID: 39965653
--
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39965925
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

0
 

Author Comment

by:sqldba2013
ID: 39991646
--
0
 

Author Comment

by:sqldba2013
ID: 40005530
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:

--
0
 

Author Comment

by:sqldba2013
ID: 39999338
--
0
 

Author Comment

by:sqldba2013
ID: 40012019
question has been answered
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Read about achieving the basic levels of HRIS security in the workplace.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now