CODEWITHSUNDEEP
sql
user2085955
user2085955

Reputation: 104

How to get non-aggregate value from a record while using MAX()?

I need to get the most recent value of a column (current_price) for a group of several IDs. I have the date/time stored in a third column and have been attempting to use the MAX() function like this:

select symid, current_price, MAX(price_date_time)
from tblHistory
group by SymID

Obviously, this is not working because I don't have current_price listed as either an aggregate function or in the group by clause. But I need the result to just simply list the current_price of the latest date/time and with which ID it is associated. Is this possible?

Upvotes: 0

Views: 46

Answers (2)

Paul Draper
Paul Draper

Reputation: 83273

If OVER / PARTITION BY queries are not supported (and they very often are not), use:

SELECT t.symid, t.current_price, t.price_date_time
FROM tblHistory t
    JOIN (
        SELECT symid, MAX(price_date_time) price_date_time
        FROM tblHistory
        GROUP BY symid
    ) t2 ON t.symid = t2.symid AND t.price_date_time = t2.price_date_time

Upvotes: 0

yswai1986
yswai1986

Reputation: 263

If your db supports analytical queries. (replace "ROW_NUMBER()" with "RANK()" to allow tie)

SELECT * FROM
(
SELECT symid, current_price, ROW_NUMBER() OVER (PARTITION BY symid ORDER BY price_date_time DESC) AS rn
FROM tblHistory
) AS T
WHERE rn = 1

Upvotes: 1

Related Questions