Image to have a table like the following and you need to get the max value of COL2 for each COL1.
In the example Pippo has as max value 1250, Pluto 7.000 and Paperino just one row with 300.
TABLE myTable
| COL1 | COL2 | COL3 |
|---|---|---|
| PIPPO | 1.250 | Home |
| PIPPO | 170 | Bike |
| PLUTO | 10 | Sandwich |
| PLUTO | 7.000 | Car |
| PAPERINO | 300 | School |
If we need to select the max value for each unique name in COL1 we can use the following select
/* Select for the col1 the max value. It could seems strange but it's correct ;-) */ select max(Col2) Col2, Col1 from myTable group by Col1
Result of Query
| COL1 | COL2 |
|---|---|
| PIPPO | 1.250 |
| PLUTO | 7.000 |
| PAPERINO | 300 |
As you can see we loose the information about the COL3.
To get the COL3 we need to join the result of previous Select to myTable. Something like the following.
/* Here the inner join */ Select tbAll.* from ( -- Previous Select select max(Col2) Col2, Col1 from myTable group by Col1 ) as tbMax inner join myTable as tbAll on tbMax.col1 = tbAll.col1 and tbMax.col2 = tbAll.col2
Result of Query
| COL1 | COL2 | COL3 |
|---|---|---|
| PIPPO | 1.250 | Home |
| PLUTO | 7.000 | Car |
| PAPERINO | 300 | School |
- have fun -
