Solved

NULL value in SQL Server

Posted on 2014-03-25
15
384 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 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 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 70

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 70

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

630 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