Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

NULL value in SQL Server

Posted on 2014-03-25
15
Medium Priority
?
393 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 500 total points
ID: 39954993
select distinct (Name) from Table_Name order by isnull(Name, '')
0
 

Assisted Solution

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

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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 71

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 49

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
 

Author Comment

by:sqldba2013
ID: 39965653
--
0
 
LVL 71

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

926 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