Changing the order of a GROUP BY in MySQL

Question

With this query I get a set of data

SELECT UID, CouncilNam, Probabilit, Scored_Lab, PTC_ID
FROM london.london

I want to group by PTC_ID and Scored_Lab which I can do with this...

SELECT UID, CouncilNam, Probabilit, Scored_Lab, PTC_ID
FROM london.london
GROUP BY PTC_ID, Scored_Lab

However, in the initial query there are multiple rows with grouped Scored_Lab and PTC_ID but differing Probability values. I want the lowest from this set but the default selection within group by doesn't provide this.

I have read dozens of similar queries on here but still cannot work it out. Any help would be appreciated.

*"I want the lowest from this set but the default selection within group by doesn't provide this."* -- there is "no default selection within group". Your query is not valid SQL. You can use [`MIN()`](https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_min) to get the lowest value for each column but if you want to get the entire row that contains the minimum then you are on the wrong path; `GROUP BY` cannot do that. — axiac, Jul 21 '17 at 10:04
Your Select statement is invalid SQL - you can not select columns that are not part of the grouping. MySQL will _tolerate_ this error when configured to do so - but you will get a value from a _random_ record in the group for those columns. _“I want the lowest from this set”_ - then simply apply https://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html the other way around. — CBroe, Jul 21 '17 at 10:05
Thank you for the responses. I think I may have solved it using this https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group — latitudehopper, Jul 21 '17 at 10:06

Tim Biegeleisen · Answer 1 · 2017-07-21T10:08:22.960

If I read your question correctly, you are looking for something along these lines:

SELECT
    PTC_UD,
    Scored_Lab,
    MIN(Probabilit)    -- take the smallest probability in each group
FROM london.london
GROUP BY PTC_ID, Scored_Lab

Note that selecting the UID and CouncilNam columns may not be valid, or may not make sense, because you did not specify them as aggregates of the groups in your query.

Here is a general query you can use to get full matching rows for each probability minimum group:

SELECT t1.*
FROM london.london t1
INNER JOIN
(
    SELECT PTC_UD, Scored_Lab, MIN(Probabilit) AS p_min
    FROM london.london
    GROUP BY PTC_ID, Scored_Lab
) t2
    ON t1.PTC_UD     = t2.PTC_UD     AND
       t1.Scored_Lab = t2.Scored_Lab AND
       t1.Probabilit = t2.p_min

Changing the order of a GROUP BY in MySQL

1 Answers1