CODEWITHSUNDEEP
mysql
latitudehopper
latitudehopper

Reputation: 775

Changing the order of a GROUP BY in MySQL

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.

Upvotes: 0

Views: 37

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521123

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

Upvotes: 1

Related Questions