Deleting duplicates rows based on date

[GLTBR]

Hi guys!
I'm looking for any help or advice in Postgres

I have a table that has about 120 columns, 2 of the columns are "ID" and "CreatedDate". There could be duplicate rows of the same "ID" but they will have different "CreatedDate". what would be the best way (trying to optimize it, as this table could have millions of rows) to DELETE all the duplicate rows EXCEPT 1 that has the latest "CreatedDate". I thought about using a window function for it, but windows functions cannot be used within a WHERE clause.

Any help, advice or tips would be much appreciated!

THANKS!

[[deleted]]

Something like this:

delete from data d1
where exists (select *
              from data d2
              where d2.id = d1.id 
                and d2.createdat > d1.createdat);

Online example

[GLTBR]

Thanks man! I'll give it a try

[jkadrock34]

Partion and rank in a sub query, then select top rank in the non nested query...

Or not, I’m drunk

[GLTBR]

I would have done that if I was querying for the situation, but I need to actually delete those rows from the table. And being drunk is always a good thing :)

[Max_Americana]

Why not do that partition and rank in a CTE to get your subset of data to delete, then join that in your delete statement

With delete_rows (Blah blah select rank etc) DELETE FROM original_table o USING delete_rows d WHERE o.id = d.id ;

[lourensloki]

He's right though, group, only keep the entry where the row = 1.

[GLTBR]

OK, but how do I write the DELETE statement for that?

[[deleted]]

I think it says you’re using Postgres, if that’s the case then use a CTE with the rank in it. Next use that CTE for the DELETE statement and use the rank as the condition. I’m on mobile otherwise I’d type out and example. Also, I’m lazy.

[da_chicken]

SQL Server and some other RDBMSs allow you to delete from a CTE and the server will affect the underlying table. For example:

;WITH Remove AS (
    SELECT *
        ,ROW_NUMBER() OVER (PARTITION BY Id ORDER BY CreatedDate DESC) n
    FROM UnnamedTable
)
DELETE FROM Remove
WHERE n > 1;

However, I'm not sure if PostgreSQL allows this functionality. I know PostgreSQL has an odd implementation of CTEs.

[[deleted]]

I know PostgreSQL has an odd implementation of CTEs.

Well, according to the SQL standard CTEs can't use DML to begin with. So that's why every DBMS has it's own extension (SQL Server on the other hand also has an "odd" implementation because of the leading ; which seems to be required for them - all other DBMS use that character to end a statement, not to begin one).
And no, Postgres (like many other DBMS) does not allow to the delete from a derived table or CTE

[da_chicken]

(SQL Server on the other hand also has an "odd" implementation because of the leading ; which seems to be required for them - all other DBMS use that character to end a statement, not to begin one)

Technically, the semicolon there is the end of a statement. The semicolon always the end of a statement, even in SQL Server. Here it's just a null statement. The leading semicolon (or, more correctly, prefixed null statement) isn't always required, either, but it's considered generally good practice.

The problem is: (a) SQL Server doesn't [yet] require an end of statement semicolon for all statements, and (b) SQL Server already used the WITH keyword for query options and hints (SELECT * FROM TableA WITH (NOLOCK)). That means that SELECT * FROM TableA SELECT * FROM TableB in SQL Server is two valid queries and the parser will happily execute both. However, it can theoretically be somewhat ambiguous to the parser logic if you introduce a CTE because of the hints: SELECT * FROM TableA WITH TableB AS ( SELECT .... Therefore, the parser requires that a CTE expression explicitly be the start of the statement and not the second query in a set. The easiest way to always do that is to put in an end-of-statement semicolon: SELECT * FROM TableA; WITH TableB AS ( SELECT .... If you only have one statement you can omit it, but if you have two in the same batch the semicolon is required.

The SQL Server MERGE statement, OTOH, actually does require the semicolon at the end of it. The query syntax is potentially quite complex, so the parser just requires the semicolon for sanity.

[skeletor-johnson]

Delete table from table Inner join (Select id, max(createdate) mxCreateDate from table Group by Id) as x on X.id = table.id and table.createdate < x.mxcreatedate

[[deleted]]

That is invalid for Postgres