Mysql count only higest values per row

Question

I can't resolve this problem

I have 2 tables and I want to count highest version per country for all users

Example:

John is from the USA, and he has versions 1,2,3,4,5 and 6

Bob is from France, and he has versions 1,2 and 3

Mary is from France too, and she has versions 1,2 and 3

I want to count just the last versions from anny of them

Becouse :

John has higher version 6 he won't be counted in versions lower than 6

Bob has higher version 3 he won't be counted in versions lower than 3

Mary has higher version 3 she won't be counted in versions lower than 3

So the final result will be as the Desired result set at bottom

Table1

ID | USER  |  COUNTRY
---- ----- ----------
1  |  John |  USA
2  |  Bob  |  FRANCE
3  |  Mary |  FRANCE

Table2

ID |  USER  | VERSION
---- ------- ---------
1  |  John  |  1
2  |  Bob   |  1 
3  |  John  |  2 
4  |  Mary  |  1 
5  |  Mary  |  2 
6  |  John  |  3 
7  |  John  |  4 
8  |  Bob   |  2 
9  |  John  |  5 
10 |  Bob   |  3 
11 |  Mary  |  3 
12 |  John  |  6

Desired result set: 

Country | V1| V2 | V3 | V4 | V5 | V6 
-------- ---- --- ---- ---- ---- ----
USA     | 0 | 0  | 0  |  0 | 0  |  1 
FRANCE  | 0 | 0  | 2  |  0 | 0  |  0

I made some try without luck

SELECT`Table1`.`Country` AS `C`,
    count(max( `Table2`.`Version` )) AS `V`
FROM
    ( `Table1` JOIN `Table2` ON ( ( `Table1`.`USER` = `Table2`.`USER` ) ) ) 
GROUP BY
    `Table1`.`COUNTRY`



OR SOMETHING LIKE THIS


SELECT
    `Table1`.`COUNTRY` AS `COUNTRY`,
    count(
    DISTINCT
    IF
        (
            (
                NOT (
                    `Table2`.`VERSION` IN (
                    SELECT
                        `Table2`.`VERSION` 
                    FROM
                        ( `Table1` JOIN `Table2` ON ( ( `Table1`.`USER` = `Table2`.`USER` ) ) ) 
                    WHERE
                        ( `Table2`.`VERSION` < 2 ) 
                    ) 
                ) 
            ),
            `Table2`.`ID`,
        NULL 
        ) 
    ) AS `V2`
FROM
    ( `Table1` JOIN `Table2` ON ( ( `Table1.`USER` = `Table2`.`USER` ) ) ) 
GROUP BY
    `Table1`.`COUNTRY`

Please help me , thanks

What have you tried so far and where did you get stuck? You seem to need max version per user, then a dynamic pivot by country. Both problems have already been answered here on SO... — Shadow, Oct 03 '21 at 14:26
Can you help me find the answered solution about dynamic pivot by country. Seem I'm lost ... — stav, Oct 03 '21 at 16:16
https://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns I really do wonder what you could have searched for that you could not find the linked question based on dynamic pivot keywords — Shadow, Oct 03 '21 at 18:52

score 0 · Answer 1 · answered Oct 03 '21 at 19:14

I found solution, thanks!

SELECT a.country
     , sum(b.version=1) as V1
     , sum(b.version=2) as V2
     , sum(b.version=3) as V3
     , sum(b.version=4) as V4
     , sum(b.version=5) as V5
     , sum(b.version=6) as V6
FROM table1 a
     JOIN (
            SELECT user
                 , MAX(version) as version
            FROM table2
            GROUP BY user
          ) b USING (user)
GROUP BY country
ORDER BY country DESC;

Mysql count only higest values per row

1 Answers1