'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
mysqlquery-optimizationdatabase-performancegroupwise-maximum


Solution 1:[1]

The most performant solution where you don't need to self-join is to use a window function, optionally with a cte although a sub-query is fine also.

Unfortunately row_number() is supported only from version 8.0, however including here for completeness and to show why you should upgrade!

    with latest as (
        select * , Row_Number() over(partition by technology order by date desc) rn
        from t
    )
    select Technology, Students, Amount, Area, Date
    from latest
    where rn=1

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Stu

Related Questions