Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    12

    Unanswered: Tricky order problem!...

    Here is what I am trying to do...

    My table consists of a field called status whose values are "close", "open", "open/close", "immediate".

    The requirement is to order them NOT by alphabetical, but by the following order.
    1. Open
    2. Close
    3.Open/Close
    4. Immediate

    As I have hard limitations at the front end, I can not perform this ordering there. The only option is to order them at query level. Is there a possibility?

    Any help is highly appreciated!.......

  2. #2
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    how about a temp table for each of 4 SARG's and final query that is UNION of all 4.
    Last edited by willy_and_the_ci; 03-02-06 at 00:19.

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Use case statement e.g.
    Code:
    create table #t1 (f1 int not null, status varchar(10) not null)
    
    insert into #t1
    select 1,'Close' union all
    select 2,'Immediate' union all
    select 3,'Open' union all
    select 4,'Open/Close' union all
    select 5,'Close' union all
    select 6,'Open'
    
    (6 rows affected)
    
    
    select f1,status
    from #t1
    order by case
    when status='Open' then 1
    when status='Close' then 2
    when status='Open/Close' then 3
    when status='Immediate' then 4
    end
    
    (6 rows affected)
    
    f1          status     
    ----------- ------     
              3 Open       
              6 Open       
              1 Close      
              5 Close      
              4 Open/Close 
              2 Immediate

  4. #4
    Join Date
    Feb 2004
    Posts
    12

    Tricky order problem!...

    Perfect!..

    I used the case statement and got the required result set. Thanks a lot!

Posting Permissions

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