mysqlquery-optimizationdatabase-performancegroupwise-maximum

how to retrieve latest data from mysql table out of duplicate records

I am trying to retrieve latest data from my sql table for each record. There will be duplicate data for each record with some data changes. I need to retrieve the latest timestamped data. Can someone suggest which is the optimum solution in terms of performance. Have seen some solutions with inner joins and sub queries.

Sample data given below

Technology Students Amount Area      Date
python     500      1000   Bangalore 2021-08-06 12:03:26
Ruby       100      1000   Bangalore 2021-08-06 05:18:50
Java       300      1000   Bangalore 2021-08-06 18:23:40
python     900      1000   Bangalore 2021-08-06 16:23:30
Java       100      1000   Bangalore 2021-08-06 12:23:50
Ruby       500      1000   Bangalore 2021-08-06 15:13:40

my o/p should contain latest data for each tech

Technology Students Amount Area      Date
Java       300      1000   Bangalore 2021-08-06 18:23:40
python     900      1000   Bangalore 2021-08-06 16:23:30
Ruby       500      1000   Bangalore 2021-08-06 15:13:40
Solution

  • One way to do this:- *Replace Table with real table Name.

    select table.* 
from table
join 
(
    select Technology, max(Date) as max_dt 
    from table
    group by Technology
) t
on table.Technology= t.Technology and table.Date = t.max_dt