CASE question

[IBazz]

Hi,

The following query returns the values I want but I want to order the output by some form of numericism (dats me word for a tuesday lol).

The name_of_timespan could be 'May Day holiday' or it could ne 'Normal hours'

So how do I order by those, numerically and return the value of the column as well? I seem to be able to do either but not yet both.

returning the values

SELECT 
          bh.hours_id
        , bh.start_date
        , bh.end_date
        , bh.name_of_timespan 
        , bh.hours_type
        , bhd.day_of_week
        , bhd.shift_number
        , bhd.opening AS opening
        , bhd.opening_or_closing
        , bhd2.opening AS closing
     FROM business_hours AS bh
   inner 
     JOIN business_hours_data as bhd
       ON bh.hours_id = bhd.hours_id 
      and bhd.opening_or_closing = 'opening'	   
   left outer
     JOIN business_hours_data as bhd2
       ON bhd2.hours_id = bh.hours_id
      AND bhd2.day_of_week = bhd.day_of_week
      and bhd2.shift_number = bhd.shift_number	
      and bhd2.opening_or_closing = 'closing'
    WHERE bh.business_id = ?
      and bh.end_date >= curdate()
        order
           by bh.name_of_timespan, bh.start_date

correct CASE statement

SELECT 
           bh.hours_id
         , bh.start_date
         , bh.end_date
         , case when bh.name_of_timespan = 'Normal opening hours' then 1
               else 2 end AS stuff
         , bh.hours_type
         , bhd.day_of_week
         , bhd.shift_number
         , bhd.opening AS opening
         , bhd.opening_or_closing
         , bhd2.opening AS closing
 
      FROM business_hours AS bh
 
  inner 
      JOIN business_hours_data as bhd
        ON bh.hours_id = bhd.hours_id 
       and bhd.opening_or_closing = 'opening'	   
  left outer
      JOIN business_hours_data as bhd2
        ON bhd2.hours_id = bh.hours_id
       AND bhd2.day_of_week = bhd.day_of_week
       AND bhd2.shift_number = bhd.shift_number	
       AND bhd2.opening_or_closing = 'closing'
   WHERE bh.business_id = ?
       AND bh.end_date >= curdate()
         ORDER   
            BY bh.name_of_timespan, bh.start_date

SO; how do I return the ordering number as well as the column values?

bazz

I did read the docs and search in SP but I am not finding it.

bazz

[guido2004]

Try

ORDER BY
    CASE WHEN bh.name_of_timespan = 'Normal opening hours' THEN 1
            ELSE 2 
    END
  , bh.start_date

[IBazz]

Thanks guido.

I had done it that way firstly and it returns just 1 or 2. What I need is for the col value to be returned as well so I can use it in my page output.

bazz

[IBazz]

oops, I overlooked the ORDER BY in your response.

I'll try that now.