SELECT query for skipping rows with duplicates but leaving the first and the last occurrences in PostgreSQL

2501 views sql
-1

I have a table with items, dates, and prices and I am trying to find a way to write a SELECT query in PostgreSQL which will skip rows with duplicate prices so that, only the first and last occurrence of the same price in a row would stay. After the price change, it can go back to the previous value and it should be preserved as well.

id   date        price   item
1    20.10.2018  10      a
2    21.10.2018  10      a
3    22.10.2018  10      a
4    23.10.2018  15      a
5    24.10.2018  15      a
6    25.10.2018  15      a
7    26.10.2018  10      a
8    27.10.2018  10      a
9    28.10.2018  10      a
10   29.10.2018  10      a
11   26.10.2018  3       b
12   27.10.2018  3       b
13   28.10.2018  3       b
14   29.10.2018  3       c

Result:

id   date        price   item
1    20.10.2018  10      a
3    22.10.2018  10      a
4    23.10.2018  15      a
6    25.10.2018  15      a
7    26.10.2018  10      a
10   29.10.2018  10      a
11   26.10.2018  3       b
13   28.10.2018  3       b
14   29.10.2018  3       c

answered question

1 Answer

11

You can use lag() and lead():

select t.*
from (select t.*,
             lag(price) over (partition by item order by date) as prev_price,
             lead(price) over (partition by item order by date) as next_price
      from t
     ) t
where prev_price is null or prev_price <> price or
      next_price is null or next_price <> price

posted this

Have an answer?

JD

Please login first before posting an answer.