Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

NULL value in SQL Server

Posted on 2014-03-25
15
Medium Priority
?
387 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 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

730 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