--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@k...> wrote:

> --- In firebird-support@yahoogroups.com, "ra8009" wrote:
> > The tables and fields I need are:
> >
> > PRODUCTS (PRODUCT_ID) - just one per product ordered
> > ORDERS (ORDER DATE) - just the most recent
> > ORDER_ITEMS (PRICE) - higher price used to break ORDER_DATE "ties"
> >
> > The only problem I have is that I'm getting all records, not just
> > the latest/highest price one. The MAX in the HAVING clause seems to
> > have no effect???
>
> I disagree, the main problem here (I haven't read all the former
> correspondence, but this description is enough to understand) is that
> whomever wrote the SQL either doesn't have a clear understanding of
> your requirements or expects the computer to understand what you want
> rather than what you ask for. I'll start by writing plain english:
>
> You want one record per product_id. Then either select distinct or
> group by product_id, in your case you want group by.
>
> You want the latest date, then select max(order_date).
>
> You want the highest price. Normally that would have been max(price),
> but in your case you don't want the highest ever price for the
> product, just the highest price for the product on a given date -
> hence you need a (somewhat complicated) subselect.
>
> Here's an attempt to write the SQL you then need:
>
> SELECT
> P.PRODUCT_ID,
> MAX(O.ORDER_DATE),
> (SELECT MAX(I2.PRICE) //Find max price
> FROM ORDER_ITEMS I2
> JOIN ORDERS O2
> ON I2.ORDER_ID = O2.ORDER_ID
> JOIN CUSTOMER C2
> ON C2.CUSTOMER_ID = O2.CUSTOMER_ID
> WHERE P2.PRODUCT_ID = P.PRODUCT_ID //for the same product
> AND C2.CUSTOMER_ID = C.CUSTOMER_ID //and customer
> AND NOT EXISTS(SELECT * FROM PRODUCTS P3 //without a later date
> JOIN ORDER_ITEMS I3
> ON P3.PRODUCT_ID = I3.PRODUCT_ID
> JOIN ORDERS O3
> ON I3.ORDER_ID = O3.ORDER_ID
> JOIN CUSTOMER C3
> ON C3.CUSTOMER_ID = O3.CUSTOMER_ID
> WHERE C3.CUSTOMER_ID = 10300
> AND P3.PRODUCT_ID = P2.PRODUCT_ID
> AND O3.ORDER_DATE > O2.ORDER_DATE)) AS MAXPRICE
> FROM PRODUCTS P
> JOIN ORDER_ITEMS I
> ON P.PRODUCT_ID = I.PRODUCT_ID
> JOIN ORDERS O
> ON I.ORDER_ID = O.ORDER_ID
> JOIN CUSTOMER C
> ON C.CUSTOMER_ID = O.CUSTOMER_ID
> WHERE
> C.CUSTOMER_ID = 10300
> GROUP BY P.PRODUCT_ID
>
> I got quite confused while writing this, so it may not be 100%
> correct. It may also be possible to simplify a bit, but you'll have to
> examine for yourself whether that is possible. I think all this
> probably could have been written much simpler in a stored procedure.
>
> HTH,
> Set

How could I approach this using a stored procedure? Does that allow me
to query data from another query avoiding the sub-selects?