Postgresql Using Limit with Order by without select and where case

Question

So I want to get the latest 2 rows of the person_id and check if the deposit field is decreased.

Here is what I have done so far;

The customer table is;

   person_id  employee_id  deposit  ts 
    101        201         44        2021-09-30 10:12:19+00
    101        201         47        2021-09-30 09:12:19+00
    101        201         65        2021-09-29 09:12:19+00    
    100        200         21        2021-09-29 10:12:19+00
    104        203         54        2021-09-27 10:12:19+00

and as a result I want is;

   person_id  employee_id  deposit  ts                       pre_deposit   pre_ts
    101        201         44        2021-09-30 10:12:19+00    47          2021-09-30 09:12:19+00

I don't want to get deposit:65 row because I just want to check the last 2 rows. 47 > 44 so. I need to compare only the last 2 rows. if the deposit decreased in any other rows, I simply don't care.

    SELECT person_id, 
           employee_id,
           deposit,
           ts,
           lag(deposit) over client_window as pre_deposit,
           lag(ts) over client_window as pre_ts
    FROM customer 
    WINDOW client_window as (partition by person_id order by ts)
    ORDER BY person_id , ts

so it returns a table with the following results;

   person_id  employee_id  deposit  ts                       pre_deposit   pre_ts
    101        201         44        2021-09-30 10:12:19+00    47          2021-09-30 09:12:19+00 
    101        201         47        2021-09-30 09:12:19+00    65        null 
    100        200         21        2021-09-29 10:12:19+00    null        2021-09-29 09:12:19+00
    104        203         54        2021-09-27 10:12:19+00    null        null

but if I do the following;

SELECT person_id, 
       employee_id,
       deposit,
       ts,
       lag(deposit) over client_window as pre_deposit,
       lag(ts) over client_window as pre_ts
FROM customer 
WINDOW client_window as (partition by person_id order by ts limit 2)

this query doesn't work, because it throws an error as;

ERROR:  syntax error at or near "limit"
LINE 11:    limit 2

so how can I limit to compare the last 2 rows? where pre_deposit > deposit

The Impaler · Accepted Answer · 2021-10-13 16:58:44Z

1

You can do:

with
data as (
  select person_id, employee_id, deposit, ts,
    row_number() over(partition by person_id order by ts desc) as rn
  from customer
)
select a.*,
  b.deposit as pre_deposit,
  b.ts as pre_ts
from data a
left join data b on a.person_id = b.person_id and b.rn = 2
where a.rn = 1

Result:

 person_id  employee_id  deposit  ts                        rn  pre_deposit  pre_ts                   
 ---------- ------------ -------- ------------------------- --- ------------ ------------------------ 
 100        200          21       2021-09-29T10:12:19.000Z  1   null         null                     
 101        201          44       2021-09-30T10:12:19.000Z  1   47           2021-09-30T09:12:19.000Z 
 104        203          54       2021-09-27T10:12:19.000Z  1   null         null

See running example at DB Fiddle.

answered Oct 13, 2021 at 16:58

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

kuzua Over a year ago

thanks for your time and reponse! but I don't want to get the rows if pre_deposit and pre_ts are null. So for person_id:100 and person_id:104, I need to filter those.

kuzua Over a year ago

and b.pre_deposit != '' returns with err: error: column b.pre_deposit does not exist

The Impaler Over a year ago

Then change the left join to just join. See example: db-fiddle.com/f/eUAUNnUN3fyZ5dSAFdu11t/1

Stefanov.sm · Accepted Answer · 2021-10-13 18:12:22Z

1

You are pretty close. Use this brilliant distinct on on your first query slightly modified and you are there.

select distinct on (person_id) *
from 
(
 select person_id, employee_id, deposit, ts, 
        lead(deposit) over w as pre_deposit, 
        lead(ts) over w as pre_ts 
 from customer
 window w as (partition by person_id order by ts desc)
) t 
where pre_deposit > deposit
order by person_id, ts desc;

SQL Fiddle here.

edited Oct 13, 2021 at 18:12

answered Oct 13, 2021 at 17:51

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Collectives™ on Stack Overflow

Postgresql Using Limit with Order by without select and where case

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related