CODEWITHSUNDEEP
sqlms-accessvba
area9
area9

Reputation: 391

MS Access SQL Deleting from grouped field by max value when there is more than one

I am using SQL queries on a MS Access database and have a table like the following:

Team           Country  City     Firstname  Surname     RecordedDateTime
Group Panda 7  USA      New York Jim        Smith       12/07/2016 09:10:06
Group Tiger 5  USA      Boston   Jack       Thompson    05/07/2016 19:15:21
Group Goat 12  USA      New York Jim        Smith       12/07/2016 09:10:06
Group Lion 9   France   Paris    Tom        Pryor       06/07/2016 10:10:08
Group Eagle 2  USA      New York Dale       Wycliff     12/07/2016 12:28:09
.....

Now, I'm trying to delete the records with: the latest date/time (so would be more than one maximum); have the same Country, City, Firstname and Surname; those that don't have "Panda" in the Team, or if there's no "Panda" then those that don't have "Goat" in the Team, or if there's no "Goat" then those that don't have "Lion" in the Team and so on. So for Jim Smith, I want to delete the third record displayed.

Hopefully, that makes sense. So far, I can delete records older than the latest date/time with the following:

DELETE DISTINCTROW T1.* FROM Results T1 LEFT JOIN (SELECT Country, City, Firstname, Surname, MAX(RecordedDateTime) AS LatestTime
FROM Results GROUP BY Country, City, Firstname, Surname) T2 ON T1.Country = T2.Country AND T1.City = T2.City
AND T1.Firstname = T2.Firstname AND T1.Surname = T2.Surname WHERE T1.RecordedDateTime <> T2.LatestTime

But the problem is handling more than one maximum and the conditions for part of a field.

Upvotes: 0

Views: 54

Answers (1)

Parfait
Parfait

Reputation: 107567

Consider using WHERE clause subqueries for the delete query to be updateable and hence deleteable. Your needs are a bit unusual as you require a specific team order that is neither numeric or string ordered. Therefore, a nested IIF() is used to give such an ordering with Pandas (lowest) < Tiger < Goat < Lion < Eagle (highest). 

DELETE *
FROM Results T1 
WHERE (T1.RecordDateTime <>

    (SELECT MAX(RecordDateTime) AS LatestTime
     FROM Results sub
     WHERE sub.Country = T1.Country 
     AND sub.City = T1.City
     AND sub.Firstname = T1.Firstname 
     AND sub.Surname = T1.Surname 
     GROUP BY Country, City, Firstname, Surname))

OR (NOT IIF(Team LIKE '*Panda*', 1, 
            IIF(Team LIKE '*Tiger*', 2, 
                IIF(Team LIKE '*Goat*', 3,
                    IIF(Team LIKE '*Lion*', 4, 
                        IIF(Team LIKE '*Eagle*', 5, NULL))))) IN

    (SELECT MIN(IIF(Team LIKE '*Panda*', 1, 
                    IIF(Team LIKE '*Goat*', 2, 
                        IIF(Team LIKE '*Goat*', 3, 
                            IIF(Team LIKE '*Lion*', 4, 
                                IIF(Team LIKE '*Eagle*', 5, NULL))))))
     FROM Results sub
     WHERE sub.Country = T1.Country 
     AND sub.City = T1.City
     AND sub.Firstname = T1.Firstname 
     AND sub.Surname = T1.Surname 
     GROUP BY Country, City, Firstname, Surname))

Should there be more team orderings, add to the conditional logic or better yet use a lookup table to be joined with Results table. This way you avoid the nested IIF() and simply use TeamValue:

Team              TeamValue
Group Panda 7             1
Group Tiger 5             2
Group Goat 12             3
Group Lion 9              4
Group Eagle 2             5
...

Upvotes: 1

Related Questions