CODEWITHSUNDEEP
mysqlsqldategreatest-n-per-groupwindow-functions
DoreenBZ
DoreenBZ

Reputation: 318

Filtering values according to a different value for each user

I am trying to understand how to do in mySQL what I usually do in python.

I have a sales table, with sale_date, user_id and price_USD as columns. Each row is an order made by the user. I want to get a new table that has all of the orders which cost more than the last order the user made (so in this picture, just the yellow rows).

I know how to get a table with the last order for each user, but I cannot save it on the database. How do I compare each row's price to a different value by the user_id and get just the larger ones in one 'swoop'?

Thanks

enter image description here

Upvotes: 1

Views: 40

Answers (1)

GMB
GMB

Reputation: 222582

If you are running MysL 8.0, you can do this with window functions:

select t.*
from (
    select
        t.*,
        first_value(price_usd) 
            over(partition by user_id order by sale_date desc) last_price_usd
    from mytable t
) t
where lag_price_usd is null or price > last_price_usd

In earlier versions, you could use a correlated subquery:

select t.*
from mytable t
where t.price_usd > (
    select price_usd
    from mytable t1
    where t1.user_id = t.user_id
    order by sale_date desc
    limit 1
)

Upvotes: 1

Related Questions