Question 365864 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – how to skip previous row if specific values met using leg windowing function

This is my sample data

id|score|
--+-----+
 1|   10|
 2|   20|
 3|   30|
 4|   40|
 5|   50|
 6|   60|
 7|   30|
 8|   30|
 9|   90|
10|  100|

My expected output is

id|score|_prev_element|
--+-----+-------------+
 1|   10|         NULL|
 2|   20|           10|
 3|   30|           20|
 4|   40|           20|
 5|   50|           40|
 6|   60|           50|
 7|   30|           60|
 8|   30|           60|
 9|   90|           60|
10|  100|           90|

_prev_element – want to display Previous element ie for id=2 then value 10 for previous element. if Previous element is 30 then skip and print recent value which is not 30.
for example for id =8 previous element is 30 so i skip and pick 60
for id=9 previous element 8 and 7 ‘s are 30 so skip and pick 60. How can i achieve this result. I tried leg(score) over(order by id)..No luck

Tags:

2

Answers


  1. 0

    You can do:

    with
x as (
  select *,
    sum(case when score = 30 then 0 else 1 end) over(order by id) as s
  from t
)
select a.id, b.score
from x a
left join x b on (b.s = a.s - 1 and a.score <> 30 or b.s = a.s and a.score = 30)
             and b.score <> 30

    Result:

     id  score 
 --- ----- 
 1   null  
 2   10    
 3   20    
 4   20    
 5   40    
 6   50    
 7   60    
 8   60    
 9   60    
 10  90

    I’m sure there’s a cleaner way of doing this but I can’t think of any off the top of my head.

    See running example at db<>fiddle.

    Login or Signup to reply.
  2. 0

    Try this option without window functions.

    SELECT *, 
  (
      SELECT score 
      FROM scores sp
      WHERE sp.id < s.id AND sp.score <> 30 
      ORDER BY sp.id desc
      LIMIT 1
  ) _prev_element
FROM scores s

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search