Stampa
Categoria: SQL
Visite: 16777
Stella inattivaStella inattivaStella inattivaStella inattivaStella inattiva
 

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

COL1COL2COL3
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

COL1COL2
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

COL1COL2COL3
PIPPO 1.250 Home
PLUTO 7.000 Car
PAPERINO 300 School

 

 

 - have fun -