Post reply

problems with order by/case statements and distinct

  • August 1, 2005 at 10:31 am

    #89323

    hey there havign some problems using order by with a distinct when my order by contains a case statement.

     

    i know order by items must appear in the select list if distinct is used which isnt a problem as im using a report writing tool that will allow me not to include extra fields. however when i copy and paste my order by statement, it still doesnt work..

     

    select distinct

    arrangements.description,

    arrangements.number_sql as arrno

    from events inner join arrangements on events.arrangement_number = arrangements.number_sql

    where  events.room_number in (61,59, 50)

    and events.performance = -1

    order by

    case when lower(@@Orderby) = 'date' then CONVERT(CHAR, events.date, 112)

     when lower(@@Orderby) = 'series' then blah blah

    else end

     

    help!

  • August 1, 2005 at 11:24 am

    #578633

    case statements on order by needs all columns 'returned' to be of the same type or an implicit conversion should be possible

     


    * Noel

  • August 1, 2005 at 11:57 pm

    #578724

    you have 2 problems :

    1 you can't use distinct with order by

    2 your case is not good

     

    1 you need to make a virtual table like vt.xxx,vt.yyy ..... from(

    2 after evry case you need to do else null ,

    like this

     case when  @OrderByHotels = 'paxSort'  then  orderer_name    else null   end,

    case when  @OrderByHotels = 'hotelSort'  then  hotel_name    else NULL   end,

    case when  @OrderByHotels = 'priceSort'  then  totalCost    else NULL   end,

    GST

  • August 2, 2005 at 6:18 am

    #578797

    Declare @a int

    , @s varchar(1000)

    set @a=2                          -- Say some value or parameter

    Select @s-2 =

    'select distinct convert(varchar(10),au_id),au_fname from authors

    order by ' +

     Case (@a)

     when 1 then 'convert(varchar(10),au_id)'

     when 2 then 'au_fname'

     End

    Exec (@s)

  • August 2, 2005 at 7:06 am

    #578813

    use group by instead, eg

    select arrangements.description,arrangements.number_sql as arrno,[col1],col[2],[coln]

    from events inner join arrangements on events.arrangement_number = arrangements.number_sql

    where events.room_number in (61,59, 50)

    and events.performance = -1

    group by arrangements.description,arrangements.number_sql as arrno,[col1],col[2],[coln]

    order by

    case lower(@@Orderby)

    when 'date' then CONVERT(varchar, events.date, 112)

    when '?' then cast([col1] as varchar)

    when 'series' then cast([series] as varchar)

    end

    Far away is close at hand in the images of elsewhere.
    Anon.

  • August 2, 2005 at 7:14 am

    #578818

    thanks for the feedback guys, i think David's suggestion lokos most promising given the tool i have to use..

     

    ta,

    Alex

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply