Postgres select max id only if value of another column is negative-CodePudding

So i need to write a query that will return some values but only if the latest record in that table for any user is less than 0. Here is what if been playing around with:

select 
    et.id,
    et.user_id,
    et.amount,
    et.trans_type,
    COALESCE(et."endingBalance", 0) AS current_balance,
    et."processId",
    upu.email,
    et.created_at
from "users-permissions_user" upu
join employer_transactions et 
    on upu.id = et.user_id 
    and et.id = (
        select max(et2.id) from employer_transactions et2 where et2."endingBalance" < 0
    )

and this is what is is returning:

id	user_id	amount	trans_type	current_balance	email	created_at
1946	333	150	CREDIT	-900.31	...	...

but if i run this query to test that query for that user_id:

select 
    id, 
    user_id,
    amount , 
    trans_type, 
    "endingBalance"
from employer_transactions et 
where user_id = 333 
order by id desc;

here is what i see:

id	user_id	amount	trans_type	ending_balance
1952	333	3	DEBIT	1297.31
1951	333	1	DEBIT	1299.31
1950	333	2	DEBIT	1298.31
1947	333	400	CREDIT	1300.31
1946	333	150	CREDIT	-900.31

so in this case what im looking for was for this query to have returned nothing because the record with the highest id is not negative

but lets say the sample data set is this:

id	user_id	amount	trans_type	ending_balance
900	333	3	DEBIT	-1297.31
899	333	1	DEBIT	1299.31
700	222	2	DEBIT	-1298.31
699	222	400	CREDIT	1300.31
600	111	150	CREDIT	900.31
599	111	150	CREDIT	-800.31

then what im looking for my query to return is

id	user_id	amount	trans_type	current_balance	email	created_at
900	333	3	DEBIT	-1297.31	...	...
700	222	2	DEBIT	-1298.31	...	...

because those were the latest records for that particular user_id and the current_balance was negative but noting for user_id: 111 becasue while yes there was a negative record but it wasnt the latest record for that user_id

CodePudding user response：

A common technique, for me, is to do a subquery that finds a desired subset. After that, do more filtering.

with ids as (
  select max(id) as last_id
  from t
  group by user_id
)

select *
from t
where end_balance < 0
  and id in (select last_id from ids)
;

The above gives desired output, given that the `id`'s are unique and form an ascending sequence.

CodePudding user response：

SELECT T.*
FROM
(
  SELECT *, RANK() OVER(PARTITION BY user_id ORDER BY id DESC) RNK 
  FROM trans_tbl
 ) T
 WHERE T.RNK = 1 AND T.ending_balance < 0;

Fiddle