SQL select rows with max value from database and group by 2 columns

Question

I have this table:

TABLE offers

 +------+--------------+----------+----------------+
 | id   | player_id    | team_id  | valore         |
 +------+--------------+----------+----------------+
 | 1    | 1            | 1        | 230            |
 | 2    | 1            | 3        | 150            |
 | 3    | 9            | 3        | 150            |
 | 4    | 1            | 5        | 100            |
 | 5    | 7            | 5        | 37             |
 | 6    | 7            | 1        | 38             |
 +------+--------------+----------+----------------+

And I expect this results, I would like to create a view like this:

+------+--------------+----------+----------------+
| id   | player_id    | team_id  | valore         |
+------+--------------+----------+----------------+
| 1    | 1            | 1        | 230            |
| 3    | 9            | 3        | 150            |
| 6    | 7            | 1        | 38             |
+------+--------------+----------+----------------+

I try with this SQL CODE:

create view...

select t1.* 
  from offers t1
       left join ( select player_id, 
                          team_id, 
                          max(valore) as valore
                     from offers
                 group by player_id, 
                          team_id) t2 
                 on t1.player_id = t2.player_id 
                    and t1.team_id = t2.team_id 
                    and t1.valore = t2.valore

But the results is the same of first table...It does not change anything. Can anyone help me?

You never have to be sorry for the grammar of a language you are learning or can't speak fluent. As long as you are trying and improve, its ok. Also, why exactly do you match `valore` — Cataklysim, Aug 28 '18 at 11:22
thx.Valore is a value of offer for player with id_player 1..2.. — MarSic, Aug 28 '18 at 11:25
yes, for each player, team_id is the id of a team who made the offer and serves for the update of other table — MarSic, Aug 28 '18 at 11:30
If your product_id for 2 rows are same and valore is also same but team id is different then what should happen? shall it show 1 row or 2 rows ? — YLG, Aug 28 '18 at 11:32

Yogesh Sharma · Accepted Answer · 2018-08-28T11:33:26.100

Your expected result doesn't suggests team_id in GROUP BY clause, it is actually based on player_id. So, remove it from GROUP BY clause & change the ON clause to t1.player_id = t2.player_id and t1.valore = t2.valore

So, Your query would be :

create view...
    select t1.*  
    from offers t1 inner join 
        (select player_id, max(valore) as valore
         from offers
         group by player_id
        ) t2 
       on t1.player_id = t2.player_id and
          t1.valore = t2.valore;

However, i would do instead :

create view v1 as
    select o.*
    from offers o
    where valore = (select max(o1.valore)
                    from offer o1
                    where o1.player_id = o.player_id
                   );

Left join returns additional columns with NULLs, INNER JOIN looks fine here — Kiryl Khadanovich, Aug 28 '18 at 11:32

SQL select rows with max value from database and group by 2 columns

1 Answers1