开发者

MySQL - Ignoring values at the beginning and end of a table, with group and ordering

开发者 https://www.devze.com 2023-02-14 15:46 出处:网络
Hopefully someone can help with this... I have a table that contains the following data: Table Data *id* || *showid* || *presenterid* || *hourofday* || *dayofweek*

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
0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号