Link to home
Start Free TrialLog in
Avatar of sosamv
sosamv

asked on

Max and Group by Query

I have the following indicators table:
ID_Indicator INT Auto_Increment
ID_Type INT (Foreign Key)
Value VARCHAR
Month INT
Year INT
ID_Localization INT

I need to pull up the most recent indicators for each type, based on the month and year fields, how can i do this?? i have tried:

SELECT ID_Indicator,ID_Type, MAX(DATE(CONCAT(Year,'/',Month,'/','01'))), value
FROM tbldatolocalizacion
WHERE ID_Localization = 17
GROUP BY ID_Type

it brings me 2 field but not the latest =(

Any clues?

Thanx, jerry

ASKER CERTIFIED SOLUTION
Avatar of Roger Baklund
Roger Baklund
Flag of Norway image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of dqmq
dqmq
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sosamv
sosamv

ASKER

Thank you both! I've found the answear on other forum LOL,  but your two answers area very similar to the one i've found:

#CRAZY QUERY
SELECT ut.ID_DatoLocalizacion,dato.Nombre, ut.valor, dato.TipoValor
FROM tbldatolocalizacion as ut
inner join tbldato dato ON dato.ID_Dato = ut.ID_Dato
WHERE DATE(CONCAT(Ano,'-',Mes,'-','01'))  = (SELECT max(DATE(CONCAT(Ano,'-',Mes,'-','01'))) from tbldatolocalizacion as ut2 where ut2.ID_Dato = ut.ID_Dato and ID_Localizacion=17)
AND ID_Localizacion=17 AND dato.ShowPaginaPrincipal = 1 AND dato.IsPublic = 1

Thats working perfect!

Thanx