I have a table with versioned rows, and I want to retrieve the most recent row. The table structure is basically like this:
Code:
create table Price
(
id int,
amount numeric,
effective_date datetime,
product_id int
)
The current price for a product is the one with the most recent effective_date. I can get this in regular SQL via:
Code:
SELECT
p.*
FROM
price p
LEFT JOIN price as p2 on p.product_id = p2.product_id AND p.effective_date < p2.effective_date
WHERE
p2.id is null
Price is mapped as a persistant object, and I'm trying to port the query to HQL. However, I'm not sure how to do the left join. It doesn't seem to let me specify the columns to join on, and I'm getting parse errors. What I tried is:
Code:
SELECT
p
FROM
Price as p
LEFT JOIN Price as p2 on p.product.id = p2.product.id and p.effectiveDate < p2.effectiveDate
WHERE
p2.id is null
Is this possible? Do I need to map Price as a many-to-many to itself on product_id in order to do the join? Any help would be appreciated.
Jon
Login
FAQ
Search
