Re: question using aggregate function
Date: Wed, 18 Jul 2007 00:14:06 GMT
Message-ID: <iXcni.126549$xq1.72655_at_pd7urf1no>
David Portas wrote:
> "Mia" <nospam_at_cox.net> wrote in message news:WC9ni.3$fK1.2_at_newsfe12.phx...
>
>>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?
>>
>>-Mia
> > > > Have you thought about using a correlated subquery? >
No offence to David P who knows much more about SQL than I do and plenty else too I think, but somehow I can't imagine Codd talking about correlated subqueries. Don't know if he would have shuddered at the term, but I do. I guess in most fields, lingo eventually passes understanding.
p Received on Wed Jul 18 2007 - 02:14:06 CEST