[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

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

0
sosamv
Asked:
sosamv
2 Solutions
 
Roger BaklundCommented:
Try this:
select ID_Type,
  substring_index(data,', ',1) as period,
  substring_index(substring_index(data,', ',2),', ',-1)  as indicator,
  substring_index(data,', ',-1) as value
from (
SELECT ID_Type, MAX(CONCAT(
  DATE(CONCAT(Year,'/',Month,'/','01')),', ',
  ID_Indicator,', ', 
  value)) as data
FROM tbldatolocalizacion
WHERE ID_Localization = 17
GROUP BY ID_Type) as derived

Open in new window

0
 
dqmqCommented:
SELECT t1.* from FROM tbldatolocalizacion T1 inner join
(SELECT ID_Type,  ID_Localization, MAX(DATE(CONCAT(Year,'/',Month,'/','01'))) as  maxdate
FROM tbldatolocalizacion
GROUP BY ID_Type, ID_Localization ) T2
on t2. id_type = t1.id_type
and t2.id_localization=t1.id_localization
and t2.maxdate = DATE(CONCAT(T1.Year,'/',T1.Month,'/','01'))
WHERE ID_Localization = 17
0
 
sosamvAuthor Commented:
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
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now