CODEWITHSUNDEEP
sqlmaxsql-delete
Erik Berger
Erik Berger

Reputation: 609

Delete all rows that don't contain the max value in group

I have a twist to this popular question and accepted answer: How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

I have a table structured similarly but instead of SELECTing the rows with the highest datatime I'd like to DELETE all the rows within each ID grouping that don't contain the max datetime. The query should make the table smaller in terms of rows.

I've tried using the accepted answer's select query in a WHERE NOT EXISTS sub query but I get the error "You can't specify target table ... for update in From clause"

How would you alter this to DELETE the rows that don't meet the SELECT requirement here:

SELECT tt.*
FROM topten tt
INNER JOIN
    (
     SELECT home, MAX(datetime) AS MaxDateTime
     FROM topten
     GROUP BY home
    ) groupedtt ON tt.home = groupedtt.home AND tt.datetime = groupedtt.MaxDateTime

Upvotes: 0

Views: 2038

Answers (2)

Cookie Monster
Cookie Monster

Reputation: 475

With ID being the primary key of your table

;with
GetResults
AS
(
    SELECT tt.*
    FROM topten tt
    INNER JOIN
        (
         SELECT home, MAX(datetime) AS MaxDateTime
         FROM topten
         GROUP BY home
        ) groupedtt ON tt.home = groupedtt.home AND tt.datetime = groupedtt.MaxDateTime
)
    DELETE GetResults
    FROM topten tt LEFT JOIN GetResults ON tt.ID = GetResults.ID
    WHERE GetResults.ID IS NULL

Upvotes: 1

collapsar
collapsar

Reputation: 17238

with id being the pk of topten:

    delete
      from topten tt1
     where tt1.id NOT IN (
                 SELECT tt2.id
                   FROM topten tt2
             INNER JOIN (
                          SELECT home, MAX(datetime) AS MaxDateTime
                          FROM topten tt3
                          GROUP BY home
                        ) groupedtt ON tt2.home = groupedtt.home AND tt2.datetime = groupedtt.MaxDateTime
           )
         ;

Upvotes: 0

Related Questions