CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
Rohan Nayani
Rohan Nayani

Reputation: 43

Maximum date row postgresql

I want to find out a rows which have maximum date by product,

click to see table

I want to find out a maximum date of rows by product_id

click here to see what i want

I have tried a DISTINCT but can't succeed!

Upvotes: 1

Views: 7780

Answers (2)

mohan111
mohan111

Reputation: 8865

select * from (
   SELECT *, row_number() over (partition by Product_ID order by date desc) r 
   FROM table 
) T
WHERE T.r=1

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522787

One canonical way of doing this is to use a subquery to identify the records corresponding to the maximum dates for each product ID, and then restrict the original table via an INNER JOIN.

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT PRODUCT_ID, MAX(DATE) AS DATE
    FROM yourTable
    GROUP BY PRODUCT_ID
) t2
    ON t1.PRODUCT_ID = t2.PRODUCT_ID AND
       t1.DATE       = t2.DATE

Another way to do this would be via a window function.

Upvotes: 3

Related Questions