last, first, etc

[The Iconoclast]

Greetings!
I have a table with the following fields:
Product Quantity Price Date
A 75 2.50 09/01/2005
A 35 3.50 10/03/2005
B 20 1.75 10/01/2005
C 10 3.00 09/01/2005
etc.

I am trying to formulate a query such that based on a specified date (say
09/15/2005) only the corresponding price for that particular product would
show up (i.e., A - 75 - 2.50 and C - 10 - 3.00). if the date were 10/05/2005
then the display would show (A - 110 - 3.50; B - 20 - 1.75; C - 10 - 3.00).
I've tries using the LAST aggregate function in the QBE but the results get
jumbled up.

Please help me. Thank you in advance.

 

[George Nicholson]

If you want smallest or largest value, use MIN and MAX.

FIRST and LAST will get you the values contained in the 1st and last records
which is only useful in a limited set of circumstances.

From JETsql Help:
FIRST and LAST "...simply return the value of a specified field in the
first or last record, respectively, of the result set returned by a query.
Because records are usually returned in no particular order (unless the
query includes an ORDER BY clause), the records returned by these functions
will be arbitrary."

HTH,

 

[Gary Walter]

"The Iconoclast"wrote:

I have a table with the following fields:
Product Quantity Price Date
A 75 2.50 09/01/2005
A 35 3.50 10/03/2005
B 20 1.75 10/01/2005
C 10 3.00 09/01/2005
etc.

I am trying to formulate a query such that based on a specified date (say
09/15/2005) only the corresponding price for that particular product would
show up (i.e., A - 75 - 2.50 and C - 10 - 3.00). if the date were
10/05/2005
then the display would show (A - 110 - 3.50; B - 20 - 1.75; C - 10 -
3.00).
I've tries using the LAST aggregate function in the QBE but the results
get
jumbled up.

Hi I,

PMFBI

Just to expound on George's sage advice....

Divide and Conquer method:
-----------------------------
Q1:

SELECT
Product,
Max([Date]) As MaxDate
FROM yourtable
WHERE [Date]<= #9/15/2005#
GROUP BY
Product;

"payoff query":

SELECT
Product,
Quantity,
Price,
[Date]
FROM yourtable INNER JOIN Q1
ON
yourtable.Product = Q1.Product
AND
yourtable.[Date] = Q1.MaxDate;

All-in-one query method:
-------------------------
SELECT
t.Product,
t.Quantity,
t.Price,
t.[Date]
FROM yourtable As t
WHERE t.[Date] =
(SELECT Max(t1.[Date])
FROM yourtable As t1
WHERE
t1.Product = t.Product
AND
t1.[Date]<=#9/15/2005#);

Apologies again for butting in...

gary