SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: CASE question

  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    CASE question

    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
    Code MySQL:
    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
    Code MySQL:
    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

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Try
    Code:
    ORDER BY
        CASE WHEN bh.name_of_timespan = 'Normal opening hours' THEN 1
                ELSE 2 
        END
      , bh.start_date

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oops, I overlooked the ORDER BY in your response.

    I'll try that now.


Bookmarks

Posting Permissions

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