handling NULL''s

Question

Post reply

handling NULL''s

technicalquery

Newbie

Points: 2
More actions
June 13, 2006 at 1:33 pm

#112514

hi,
We have a table were in one of the columns has null's and some valid
numbers-
Table - Customer
Id
name
seq
Id -1
name-xxx
seq-2
Id-2
name-yyy
seq-3
Id-3
name-zzz
seq-NULL
Id-4
name-aaa
seq-NULL
Now when we write "Select * from customer order by Seq,name " it
returns first all null's and then the rest in asc order.
Is there a way that we can first display the numbers in asc then the
null records ?
please replyback soon as it is URGENT .
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David McFarland SSChampion Points: 11815 More actions · Answer 1

SELECT

[ID]

,[Name]

,[SEQ]

FROM

[Customer]

ORDER BY

CASE WHEN [SEQ] IS NULL THEN 1 ELSE 0 END

,[SEQ]

technicalquery Newbie Points: 2 More actions · Answer 2

Hi,

Can you be a bit clear.i cannot see the complete query.

Thanks.

Sergiy SSC Guru Points: 110208 More actions · Answer 3

That's a complete query.

What else you want to see?

_____________
Code for TallyGenerator

David McFarland SSChampion Points: 11815 More actions · Answer 4

technicalquery, the code that does the trick you were looking for is the first line following the ORDER BY. It assigns the number "1" to a row with NULL in Seq, and "0" to a row without a NULL. Since that is the primary sort order, all zeroes (non null values) will come first, while all ones (NULL value) will come next. The secondary sort is on the Seq column, so all non null values will come first, in order, followed by all Null values.