Subject | Re: SQL - Most recent order for each product |
---|---|
Author | radevojvodic |
Post date | 2004-09-29T06:51:55Z |
Hi,
Would select like this help
SELECT
DISTINCT
P.PRODUCT_ID,
O.ORDER_DATE,
MAX(ORDER_ITEM.PRICE)
Group By P.PRODUCT_ID, O.ORDER_DATE,
...
--- In firebird-support@yahoogroups.com, "ra8009" <ra8009@y...>
wrote:
Would select like this help
SELECT
DISTINCT
P.PRODUCT_ID,
O.ORDER_DATE,
MAX(ORDER_ITEM.PRICE)
Group By P.PRODUCT_ID, O.ORDER_DATE,
...
--- In firebird-support@yahoogroups.com, "ra8009" <ra8009@y...>
wrote:
> Yes, you're right. From what I show here it's not needed. Iactually
> have the "real" query in descending order so I get the most recenthighest
> items at the top.
>
> Now I just need to get rid of everything but the most recent/
> price. Do you know why the MAX in the HAVING doesn't accomplishthat
> ot what would work?<alan@m...>
>
> --- In firebird-support@yahoogroups.com, "Alan McDonald"
> wrote:by
> > > SELECT
> > > DISTINCT
> > > P.PRODUCT_ID,
> > > O.ORDER_DATE,
> > > ORDER_ITEM.PRICE
> > >
> > > 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, O.ORDER_DATE, I.PRICE
> > > HAVING ORDER_DATE = MAX(ORDER_DATE)
> > > ORDER BY 1,2
> >
> > correct me if I'm wrong but the group by clause will order by -
> default.
> > there's no need for the order by clause when you are grouping by
> > Alan