[Solved] MAX(X) where X<=:param

[Villeroy]

Having a table "Prices" in embedded hsqldb, reflecting changing prices over time:

Code: Select all

ID	Date	Article	Price
1	2008-01-01	1	10
2	2008-01-02	2	20
3	2008-01-03	3	30
4	2008-03-01	1	11
5	2008-02-22	2	22

I'd like to have a parameter query to get the price of a given article at the given day.

Code: Select all

SELECT TOP 1 * FROM "Prices" WHERE "Article"=1 AND "Date"<={d '2008-01-15'} ORDER BY "Date" DESC

in direct mode returns the price with ID=1, but I'd like to use it with parameter substitution in a form. So I need something that works in parsed mode.

[Villeroy]

OK, this one seems to do the job:

Code: Select all

SELECT * FROM "Prices" WHERE ("Date" IN (SELECT MAX("Date") FROM "Prices" WHERE "Date"<=:X_Date AND "Article"=:Article)) AND "Article" =:Article