Sorting

Question

Sorting

lenardd

Default port

Points: 1435
More actions
July 11, 2003 at 12:45 am

#159747

I have a sort order column which contains nulls and values. When sorting ASC, is it possible to float the rows with values to the top (in ASC order)? Currently nulls float to the top.
Thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

quote:
I have a sort order column which contains nulls and values. When sorting ASC, is it possible to float the rows with values to the top (in ASC order)? Currently nulls float to the top.

I think only when you exclude the NULLS

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

lenardd Default port Points: 1435 More actions · Answer 2

lenardd

Default port

Points: 1435

July 11, 2003 at 3:29 pm

#465196

That is what I thought. Darn! 🙂

--Lenard

j.o.s. Ten Centuries Points: 1242 More actions · Answer 3

Think that a5xo3z1 and the nulls do float to the top. What about using a 'case when' statement and replace nulls with 'zzzz' to push them to the bottom. Pretty ugly but might help.

lenardd Default port Points: 1435 More actions · Answer 4

I just figured out how to go about it. Something like this seems to work:

SELECT ID, Name, SortOrder

FROM NameType

ORDER BY -SortOrder DESC

(Note the "-" in the order by clause.)

--Lenard

Edited by - lenardd on 07/12/2003 06:01:22 AM

martinvg SSC Veteran Points: 239 More actions · Answer 5

I think it's the "DESC" that does it, not the "-". Didn't your original posting say you wanted the rows in ASC order? In that case, you definitely need the CASE-statement or something similar, like: ORDER BY ISNULL(Column1, HIGHVALUE), where HIGHVALUE can be "ZZZZZ" or "999999" etc.. , depending on the datatype.

Mvg, MvG.

cneuhold Hall of Fame Points: 3375 More actions · Answer 6

generate your own sort key in the select statement by using one of the following statements:

select *, case when nullable_col_name is not null then 0 else 1 end sort_key

from your_table

order by sort_key, nullable_col_name

or:

select *, 0 sort_key

from your_table

where nullable_col_name is not null

union all

select *, 1 sort_key

from your_table

where nullable_col_name is null

order by sort_key, nullable_col_name

best regards,

chris.

Hans Lindgren SSChampion Points: 10496 More actions · Answer 7

I agree with MvG (no rhyme intended 😉

The CASE (or rather ISNULL) is the only way to solve your ORDER xx ASC with your NULLS in the end.

/Hans

David Burrows SSC Guru Points: 65142 More actions · Answer 8

If sorting on name for instance, I do the following

SELECT ID, Name 

FROM NameType 

ORDER BY (CASE WHEN Name IS NULL THEN 1 ELSE 0 END) ASC, Name ASC

Far away is close at hand in the images of elsewhere.
Anon.

eljeffo Ten Centuries Points: 1040 More actions · Answer 9

How about:

SELECT <rows>, COALESCE(<nullcolumn>, 'ZZZZ') AS <nullcolumn>

FROM <table>

ORDER BY <nullcolumn> ASC

lenardd Default port Points: 1435 More actions · Answer 10

Thanks all for your suggestions. Just to re-iterate, my way DOES work and sorts them in ASC order.