Sorting

  • 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.

  • 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]

  • That is what I thought. Darn! 🙂

    --Lenard

  • 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.

  • 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

  • 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.

  • 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.

  • 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

  • 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.

  • How about:

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

    FROM <table>

    ORDER BY <nullcolumn> ASC

  • 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