CODEWITHSUNDEEP
sqlpostgresql
emp
emp

Reputation: 642

Drop row that has different value from its next row in PostgreSQL

I have a table that look like this:

type      date       amount
-----------------------------
 A      17/06/2019      5
 A      18/06/2019      8
 A      19/06/2019      10
 B      17/06/2019      1
 B      18/06/2019      9
 C      17/06/2019      4

What I am trying to do is to drop last row for each type (Include a type that only has a single row as well). So, the result should look something like this: 

type      date       amount
-----------------------------
 A      17/06/2019      5
 A      18/06/2019      8
 B      17/06/2019      1

I have tried with a window function LEAD to check the next row if the type of a current row is not the same type as the next row, it will be deleted. Something like this:

DELETE FROM table 
WHERE ((LEAD(type, 1) over (partition by type order by date)) != type ));

I used partition and order by to make sure that data is sorted as it should be. But I just realized that I can't use window function inside WHERE clause. So based on what I am trying to acheive, how can I do this?

Upvotes: 0

Views: 174

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

If you want to actually delete the data:

delete from t
    where t.date = (select max(t2.date) from t t2 where t2.type = t.type);

If you just want to select the data:

select t.*
from t
where t.date < (select max(t2.date) from t t2 where t2.type = t.type);

I don't see an advantage to trying to use window functions in this case. For performance, create an index on (type, date).

Upvotes: 1

The Impaler
The Impaler

Reputation: 48850

You can use the window function ROW_NUMBER() to find those rows, as in:

delete from t
where (type, date) in (
  select type, date
  from (
    select type, date,
      row_number() over(partition by type order by date desc) as rn
  ) x
  where rn = 1
)

Upvotes: 0

Related Questions