Solved

NULL value in SQL Server

Posted on 2014-03-25
15
363 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 69

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
 

Author Comment

by:sqldba2013
ID: 39965653
--
0
 
LVL 69

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Replication question 9 42
convert null in sql server 12 33
transaction in asp.net, sql server 6 32
SQL Query Help Top 1 and Distinct? 6 22
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

776 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