Get the newest records from mysql table by column name

Question

I need to get the newest records from the below table for each indicator name, for example the GI of data 2015-11-16 and GO of 2015-11-14.

How can I achieve this?

Thanks

Answer 1

There are many ways to achieve this and is called group wise maximum https://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html

select t1.* from table_name t1
join (
 select indicator_name,max(indicator_date) as indicator_date from table_name
 group by indicator_name
)t2
on t1.indicator_name = t2.indicator_name 
and t1.indicator_date = t2.indicator_date

Answer 2

If you want the latest row for each indicator_name, then you can give a row number to each row group by the indicator_name and order by indicator_date in descending order.

Query

select t1.indicator_id, 
       t1.indicator_name, 
       t1.indicator_value, 
       t1.indicator_date
from 
(
     select indicator_id, 
            indicator_name, 
            indicator_value, 
            indicator_date, 
    (
        case indicator_name when @curA 
        then @curRow := @curRow + 1 
        else @curRow := 1 and @curA := indicator_name end 
    ) + 1 as rn 
    from tblIndicators t, 
    (select @curRow := 0, @curA := '') r 
    order by indicator_name,indicator_date desc 
)t1 
where t1.rn = 1;

SQL Fiddle

Answer 3

SELECT * from yourtable
GROUP BY indicator_name
ORDER BY indicator_date DESC

Answer 4

Try this...

SELECT * FROM table_name ORDER BY indicator_name,indicator_date DESC