Max and Group by Query

Posted on 2009-02-21
Last Modified: 2012-06-27
I have the following indicators table:
ID_Indicator INT Auto_Increment
ID_Type INT (Foreign Key)
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

it brings me 2 field but not the latest =(

Any clues?

Thanx, jerry

Question by:sosamv
    LVL 39

    Accepted Solution

    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 (
      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

    LVL 42

    Assisted Solution

    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
    LVL 3

    Author Closing Comment

    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:

    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!


    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (, A SQLite Tidbit: Quick Numbers Table Generation (…
    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now