CODEWITHSUNDEEP
mysqlgroup-by
Ben
Ben

Reputation:

Grouping problem

When you use Group By how do you keep the other fields in sync when using aggregates

Here I am trying to find the min value of col4 when col2 = xxx

select col1, col2, col3, min(col4)
from table
where col2 = 'xxx'
group by col3

I can get the minimum value in col4 but col1 is not correct but col2, col3, col4 are.

Can anyone show me how to do this ?

Thanks

Upvotes: 2

Views: 153

Answers (3)

Draemon
Draemon

Reputation: 34711

I guess you want the col1-3 corresponding to the min(col4) for each col3?

Something like:

select X.col1, X.col2, X.col3, X.col4 from table X 
join (select col3, min(col4) as mcol4 from table where col2='xxx' group by col3) as Y
on X.col3=Y.col3 and X.col4=Y.mcol4
where X.col2='xxx';

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425251

You are using non-standard MySQL extension to GROUP BY.

This query in fact reads as "for each distinct value of col3, select the minimal value of col4 along with the values of col1 and col2 from a single row of table having this value of col3 in no particular order"

Like, if we have the following data:

col1 col2 col3 col4
---- ---  ---  ----

A    A    1    1
B    B    1    2
C    C    2    3
D    D    2    4

, this query:

SELECT  col1, col2, col3, MIN(col4)
FROM    mytable
GROUP BY
        col3

will return either of the following:

col1 col2 col3 col4
---- ---  ---  ----
A    A    1    1
C    C    2    3

col1 col2 col3 col4
---- ---  ---  ----
B    B    1    1
C    C    2    3

col1 col2 col3 col4
---- ---  ---  ----
A    A    1    1
D    D    2    3

col1 col2 col3 col4
---- ---  ---  ----
B    B    1    1
D    D    2    3

i. e. it can return any value of col1 and col2 found in the rows that contribute to the corresponding group.

This is equivalent of FIRST_VALUE analytic function, but in no particular order.

Update:

To select values of col1 and col2 corresponding to the minimal value of col4 within each group, use this:

SELECT  col1, co2, col3, col4
FROM    (
        SELECT  col1, col2, col3, col4,
                COALESCE(@col3 = col3, FALSE) AS grp,
                @col3 := col3 AS nv
        FROM    (
                SELECT  @col3 := NULL
                ) vars, table
        WHERE   col2 = 'xxx'
        ORDER BY
                col3, col4
        ) q
WHERE   NOT grp

Upvotes: 3

SquareCog
SquareCog

Reputation: 19666

 select a.col3, a.col2, a.col1, a.col4
 from table as a natural join 
       (select col3, min(col4) as col4 from table
        where col2='xxx'
        group by col3 ) as b
 where a.col2 = 'xxx' -- sometimes this helps the optimizer even though it's redundant

you can get into a bit of trouble here when there may be multiple rows with the same col3, col4, and col2, but different col1s -- pretty straightforward to fix with rownums and such, but that gets db-specific.

Upvotes: 0

Related Questions