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