July 11, 2003 at 12:45 am
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.
July 11, 2003 at 1:04 am
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]
July 11, 2003 at 3:29 pm
That is what I thought. Darn! 🙂
--Lenard
July 11, 2003 at 4:46 pm
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.
July 12, 2003 at 1:33 am
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
July 14, 2003 at 2:17 am
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.
Mvg, MvG.
July 14, 2003 at 2:47 am
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.
July 14, 2003 at 4:13 am
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
July 14, 2003 at 7:11 am
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.
July 14, 2003 at 9:06 am
How about:
SELECT <rows>, COALESCE(<nullcolumn>, 'ZZZZ') AS <nullcolumn>
FROM <table>
ORDER BY <nullcolumn> ASC
July 16, 2003 at 6:14 am
Thanks all for your suggestions. Just to re-iterate, my way DOES work and sorts them in ASC order.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply