Use a sub-select getting the maximum date from the date range and including
the other where clause items:

SELECT P.ID, P.PRODUCTID, P.OUTLETID, P.VALUEDATE, P.SALEPRICE, P.FACING
FROM PRESENCE P
WHERE
(
(PRESENCE.OUTLETID IN (112169, 112170, 112171, 112172, 112173, 112174,
112175))
and
(PRESENCE.STATUS = 2)
and
(PRESENCE.VALUEDATE =
(SELECT MAX(VALUEDATE)
FROM PRESENCE
WHERE
(PRESENCE.OUTLETID IN (112169, 112170, 112171, 112172, 112173,
112174, 112175))
and
(PRESENCE.VALUEDATE >= '2003-09-01')
and
(PRESENCE.VALUEDATE < '2003-11-01')
and
(PRESENCE.STATUS = 2))
)
order by P.PRODUCTID, P.OUTLETID, P.VALUEDATE


This is the brute-force method and I'm sure there is a better way as this is
probably quite inefficient, but I'm also pretty sure this would work. You may
want to setup a trigger on the table to keep another table updated with
potential results you'd want from this query.

David Cornelius
CorneliusConcepts.com



_____

From: Jerome Bouvattier [mailto:JBouvattier@...]
Sent: Tuesday, February 24, 2004 11:22 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] SQL question


Hello,

Given the following statement :

SELECT P.ID, P.PRODUCTID, P.OUTLETID, P.VALUEDATE, P.SALEPRICE, P.FACING
FROM PRESENCE P
WHERE
(
(PRESENCE.OUTLETID IN (112169, 112170, 112171, 112172, 112173, 112174,
112175))
and
(PRESENCE.VALUEDATE >= '2003-09-01')
and
(PRESENCE.VALUEDATE < '2003-11-01')
and
(PRESENCE.STATUS = 2)
)
order by P.PRODUCTID, P.OUTLETID, P.VALUEDATE

How to modify it so that, for each [PRODUCTID, OUTLETID] combination, only
the *record* with the greatest VALUEDATE is returned ?

Thanks.

--
Jerome



[Non-text portions of this message have been removed]