Re: question using aggregate function
Date: Thu, 19 Jul 2007 01:24:56 -0700
On Jul 17, 9:29 pm, Mia <nos..._at_cox.net> wrote:
> I'm having trouble with a query concept.
> I know that:
> select max(order_date) from orders;
> will return the date of the newest order, and that:
> select supplier_id, max(order_date) from orders group by supplier_id;
> returns the newest order date from each supplier. But I'm trying to
> write a query that would return only the supplier_id of the most
> recently placed order. How would I do that? I thought maybe:
> select supplier_id, max(order_date) from orders group by supplier_id
> having max(order_date) = order_date;
> but it complains that order_date isn't a group by expression in the
> having clause.
> Any ideas how to do this?
O1 the set of all orders
O2 the set including the most recent date of order A simple intersect between O1 and O2 through the most recent date will allow to produce the desired result...Expressed in SQL
select O1.supplier_id from orders O1 inner join --> SET R1
( select max(order_date) order_date from orders ) O2 on O1.order_date = O2.order_dateReceived on Thu Jul 19 2007 - 10:24:56 CEST