Hopefully someone can help with this... I have a table that contains the following data:
Table Data
|| *id* || *showid* || *presenterid* || *hourofday* || *dayofweek*
|| 24 || 53 || 91 || 23 || 5
|| 23 || 53 || 91 || 22 || 5
|| 22 || 52 || 90 || 21 || 5
|| 21 || 52 || 90 || 20 || 5
|| 20 || 52 || 90 || 19 || 5
|| 19 || 63 || 88 || 18 || 5
|| 18 || 71 || 86 || 17 || 5
|| 17 || 71 || 86 || 16 || 5
|| 16 || 71 || 86 || 15 || 5
|| 15 || 71 || 86 || 14 || 5
|| 14 || 49 || 75 || 13 || 5
|| 13 || 49 || 75 || 12 || 5
|| 12 || 49 || 75 || 11 || 5
|| 11 || 49 || 75 || 10 || 5
|| 10 || 48 || 74 || 9 || 5
|| 9 || 48 || 74 || 8 || 5
|| 8 || 48 || 74 || 7 || 5
|| 7 || 48 || 74 || 6 || 5
|| 6 || 54 || 91 || 5 || 5
|| 5 || 54 || 91 || 4 || 5
|| 4 || 54 || 91 || 3 || 5
|| 3 || 54 || 91 || 2 || 5
|| 2 || 54 || 91 || 1 || 5
|| 1 || 70 || 87 || 0 || 5
HourofDay is a value between 0 and 23 which relates to the Hour of the Day (PHP Date), with Dayofweek being the number equivalent of the day of the week (PHP Date).
I currently have a query which groups all the data by Show ID and Presenter ID and then returns the data ordered by hourofday. With the group showing the first entry with the lowest hourofday value
SQL QUERY
SELECT
*
FROM
(
SELECT
*
FROM
schedule_timings
ORDER BY
hourofday ASC
) as my_table_tmp
GROUP BY
showid,
presenterid
ORDER BY
hourofday ASC
Query Result:
|| *id* || *showid* || *presenterid* || *hourofday* || *dayofweek*
|| 1 || 53 || 91 || 0 || 5
|| 2 || 54 || 91 || 1 || 5
|| 7 || 48 || 74 || 6 || 5
|| 11 || 49 || 75 || 10 || 5
|| 15 || 71 || 86 || 14 || 5
|| 19 || 63 || 88 || 18 || 5
开发者_开发问答|| 20 || 52 || 90 || 19 || 5
Now this pulls the data as would be expected. However there are values that roll over:
|| *id* || *showid* || *presenterid* || *hourofday* || *dayofweek*
|| 24 || 53 || 91 || 23 || 5
|| 23 || 53 || 91 || 22 || 5
|| 1 || 70 || 87 || 0 || 5
And the group by is grouping all of these together. But as they are seperated by other values and the hour of the day is not sequential i need these to show up seperatly.
I have had a look round and found some sequential grouping solutions (http://www.artfulsoftware.com/infotree/queries.php, search "Find blocks of unused numbers")
but i have no clue about how to best implement them.
I do not mind having to do the grouping all Server Side, but would prefer not to.
Cheers in Advance
Alan
You aren't using any aggregate functions, so I don't think you actually need Group by. Instead, I think what would work better would be a single select with multiple order bys. I think you would get the results you're looking for with:
SELECT
*
FROM
schedule_timings
ORDER BY
show_id, presenter_id, hourofday ASC
精彩评论