[Solved] SQL Delete Rows group by column names and value less than the latest column values

by


Based on my partial understanding of your question, I think this might be what you are looking for:

DELETE t
FROM SQL_TABLE t
INNER JOIN (
    SELECT max(time1) time1
        ,country
        ,STATE
    FROM sql_table
    GROUP BY country
        ,STATE
    ) q1 ON q1.country = t.country
    AND q1.[State] = t.[state]
    and q1.time1 = t.Time1
WHERE q1.time1 > t.time2

In order to find the “latest” Time1 for given Country and State, you will need to find the MAX value and group by country and state. This query will then need to be joined back to the base table using all three fields (state,country, time1) and then use a WHERE clause to limit your DELETE statement. Hope this helps!

SQL Fiddle Example

solved SQL Delete Rows group by column names and value less than the latest column values