CODEWITHSUNDEEP
mysqlsqldateselectgreatest-n-per-group
Sam
Sam

Reputation: 1139

SQL get Max Date

I need to have the last price for each product for each client . I am not really good with SQL and I don't understand how I can do it.

Data :

enter image description here

What I want :

enter image description here

It is possible to have this data with a SQL request ?

Upvotes: 0

Views: 101

Answers (2)

Fahmi
Fahmi

Reputation: 37493

One option could be a correlated subquery

SELECT product, price, date, client
FROM tablename a where date =
    (select max(date) from tablename b where a.product=b.product)

Upvotes: 1

GMB
GMB

Reputation: 222682

Use window function ROW_NUMBER(), if available in your RDBMS:

SELECT product, price, date, client
FROM (
    SELECT
        t.*,
        ROW_NUMBER() OVER(PARTITION BY product, client ORDER BY date DESC) rn
    FROM mytable t
) x
WHERE rn = 1

In MySQL < 8.0:

SELECT product, price, date, client
FROM mytable t
WHERE NOT EXISTS (
    SELECT 1
    FROM mytable t1
    WHERE t1.client = t.client AND t1.product = t.product AND t1.date > t.date
)

Upvotes: 2

Related Questions