select by max value of another column

Question

For example I have a table with price changes.

TABLE
(
ID INT,
ProductID INT,
ChangeDate DATE,
PriceOld MONEY,
PriceNew MONEY
)

I need select the actual prices for each product, i.e. select PriceNew where ChangeDate is max for each product

Answer 1

SELECT ProductID,PriceNew 
FROM
( 
     SELECT *, ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY ChangeDate DESC) RN FROM Table1
) X 
WHERE RN = 1

Answer 2

select PriceNew where ChangeDate is max for each product

I would phrase this using a correlated subquery for filtering. This seems to me like the simplest way to translate your requirement, and generally offers good performance compared to other methods (notably, window functions or self-join).

select t.*
from mytable t
where ChangeDate = (
    select max(t1.ChangeDate)
    from mytable t1
    where t1.ProductID = t.ProductID
);

For performance, you want an index on (ProductID, ChangeDate).

Answer 3

A simple method uses filtering in the where clause:

select t.productid, t.pricenew
from t
where t.changedate = (select max(t2.changedate)
                      from t t2
                      where t2.productid = t.productid
                     );

With an index on (productid, changedate), this often has better performance than other methods.

Answer 4

Analytic Window functions are your best bet -- see Vignesh Kumar A's answer which I upvoted.

If your SQL flavor does not have ROW_NUMBER() or RANK() you can accomplish the same thing with the following joins.

-- this can be problematic if there's multiple prices for the same max ChangeDate 
SELECT
    a.ID
    , a.ProductID
    , a.ChangeDate
    , a.PriceOld
    , a.PriceNew
FROM table a 
    JOIN (SELECT ProductID, max(ChangeDate) max_ChangeDate
            FROM TABLE
            GROUP BY ProductID) b
        ON b.ProductID = a.ProductID
        AND b.max_ChangeDate = a.ChangeDate

-- if IDs are monotonically increasing with time, you can exploit the following. This is better than directly using ChangeDate -- no collisions.        
SELECT
    a.ID
    , a.ProductID
    , a.ChangeDate
    , a.PriceOld
    , a.PriceNew
FROM table a 
    JOIN (SELECT ProductID, max(ID) max_ID
            FROM TABLE
            GROUP BY ProductID) b
        ON b.ProductID = a.ProductID
        AND b.max_ID = a.ID