Results 1 to 5 of 5

Thread: Order by & case

  1. #1
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809

    Question Unanswered: Order by & case

    I am trying to use a case statment to indicate sort order. Everything worked fine untill I added "desc".

    Code:
    create table #tmp(f1 varchar(25), dtObs datetime, dtCnt datetime)
    insert into #tmp values('ABC','01-Jan-2003','02-Jan-2003')
    insert into #tmp values('BCD','01-Jan-2003','03-Jan-2003')
    insert into #tmp values('CDE','01-Jan-2003','04-Jan-2003')
    insert into #tmp values('DEF','02-Jan-2003','03-Jan-2003')
    insert into #tmp values('EFG','02-Jan-2003','04-Jan-2003')
    insert into #tmp values('FGH','02-Jan-2003','05-Jan-2003')
    
    declare @Order tinyint
    set @Order = 1
    select * 
      from #tmp 
     order by case @Order when 1 then 'dtObs' 
                          when 2 then 'dtCnt' 
                          when 3 then 'dtObs desc' 
                          when 4 then 'dtCnt desc'
                          else null end
    any suggestion on how to do this?
    Paul Young
    (Knowledge is power! Get some!)

  2. #2
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Code:
    select * 
      from #tmp 
     order by case @Order when 1 then 'dtObs' 
                          when 2 then 'dtCnt' 
                          else null end
             ,case @Order when 3 then 'dtObs' 
                          when 4 then 'dtCnt' 
                          else null end DESC

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Thanks!
    Paul Young
    (Knowledge is power! Get some!)

  4. #4
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Pleasure to help you.

  5. #5
    Join Date
    Nov 2003
    Posts
    1

    Red face

    I am using a Case statement within my Order By clause, but I have columns that I want to use that were created in my Select clause as expressions. I cannot seem to use those columns in my Case statement. Is there a way to do this?? Thank you very much for your help!!!

Posting Permissions

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