DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Order By clause

  1. #1
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Hi,
    I want to display a list of countries in alphabetical order , but whether ascening or descending want to display United States first and then the rest o the list.

    How do i do it.

    Thanks
    Ronnie

  2. #2
    Join Date
    Sep 2000
    Posts
    384
    Add another column neworder to that table .For US put as A and the rest b .

    then order by neworder, ....

    Try this ...hope it will work
    Radhakrishnan.M

  3. #3
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    On what environment are you trying to do this? FORMS, WEB etc?

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    SELECT * FROM countries
    ORDER BY DECODE(country, 'USA', CHR(0), country);
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Nov 2001
    Posts
    3
    You could also try this:

    SQL> col cnt noprint
    SQL> select country, 0 AS cnt
    2 from table
    3 where country = 'UNITED STATES'
    4 union all
    5 select country, rownum AS cnt
    6 from (select country
    7 from table
    8 where country <> 'UNITED STATES'
    9 order by country )
    10 order by cnt
    11 /


  6. #6
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Originally posted by jmodic
    SELECT * FROM countries
    ORDER BY DECODE(country, 'USA', CHR(0), country);
    wow,
    It works.

    Thanks jmodic

    But why and how !!!!!!

    Ronnie

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Using DECODE in an ORDER BY statement assignes a "sorting value" to column COUNTRY for each row selected and the resultset is ordered by that "sorting values".

    In this case the record with 'USA' gets value of CHR(0) as its "sorting value", while all other records get their actual country value as their "sorting value". Remember that those "sorting values" are used only for sorting purposes, while the actual resultset being returned remains unchanged.

    Maybe the confusing part is the usage of CHR(0) expression. This is used merely because no other string can have "smaller value" than this, so this guaranties that the row with that sorting value (USA) will definitely be returned first in a sorted resultset. You could also use 'A', 'a', '1' or something similar, depending on your situation and NLS settings, while CHR(0) is the absolut "lowest value" for strings.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width