Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2013
    Posts
    6

    Unanswered: order by clause irrespective of specific record

    hi ,

    the query has been resolved thanks alot.
    Last edited by reenasharma347; 06-07-13 at 07:20. Reason: satisfied with the reply and got evry possible solution of this query hence wanted to remove this so that not to bother brain

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here's an example based on Scott's EMP table (I'm putting BLAKE to the last position). The idea is: instead of a desired value (BLAKE in my case), use something that is beyond alphabet you are using. CHR(255) certainly is (in English alphabet, at least). Sort the rest "normally" by ENAME.

    Code:
    SQL> select ename
      2  from emp
      3  order by case when ename = 'BLAKE' then chr(255)
      4                else ename
      5           end;
    
    ENAME
    ----------
    ADAMS
    ALLEN
    CLARK
    FORD
    JAMES
    JONES
    KING
    MARTIN
    MILLER
    SCOTT
    SMITH
    TURNER
    WARD
    BLAKE
    
    14 rows selected.
    
    SQL>

  3. #3
    Join Date
    Jan 2013
    Posts
    6
    thanks alot for the reply but i did'nt get chr(255) what this is functioning like .

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As I said: it is a character far beyond the last character you use for names (which, I presume, go from A to Z):
    Code:
    SQL> select level, chr(level)
      2  from dual
      3  connect by level <= 256;
    
         LEVEL C
    ---------- -
             1 ˘
             2 ˘
             3 
             4 
             5 
             6 
    ...
            48 0
            49 1
            50 2
            51 3
            52 4
            53 5
            54 6
            55 7
            56 8
            57 9
            58 :
            59 ;
            60 <
            61 =
            62 >
            63 ?
            64 @
            65 A
            66 B
            67 C
            68 D
            69 E
    ...
           119 w
           120 x
           121 y
           122 z
           123 {
           124 |
           125 }
           126 ~
    ...
           249 ů
           250 
           251 ű
           252 
           253 
           254 ţ
           255 ˙
           256
    
    256 rows selected.
    
    SQL>
    If there's noone whose name is, say 'ZZZZ TOP', this might be OK as well:
    Code:
    SQL> select ename
      2  from emp
      3  order by case when ename = 'BLAKE' then 'ZZZ'
      4                else ename
      5           end;
    
    ENAME
    ----------
    ADAMS
    ALLEN
    CLARK
    FORD
    JAMES
    JONES
    KING
    MARTIN
    MILLER
    SCOTT
    SMITH
    TURNER
    WARD
    BLAKE
    
    14 rows selected.
    
    SQL>

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    please do not remove the original question. If someone has a similar problem they can get help. Now there is noting for them to reference.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by reenasharma347 View Post
    hi ,

    had a query regarding order by clause .

    i have one table having columns id and name want to sort the table by name but keeping the name like "beta" in last position and rest of the records should be in ascending order.

    Please help me out is it possible anyway.
    Another possible solution that I prefer is something like:
    Code:
    SQL> select ename
      2  from emp
      3  order by case when ename = 'BLAKE' then 1
      4                else 0
      5           end, ename;
    
    ENAME
    ----------
    ADAMS
    ALLEN
    CLARK
    FORD
    JAMES
    JONES
    KING
    MARTIN
    MILLER
    SCOTT
    SMITH
    TURNER
    WARD
    BLAKE
    
    14 rows selected.
    
    SQL>
    The advantage to doing it this way is that there isn't any "magic" like Chr(255) so it works in multiple character sets, and this is also more flexible since you can change the code in the CASE statement to a LIKE expression to filter a whole class of rows to the end of the SELECT list and you also still get the benefit of ordering of those rows too!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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