Re: question using aggregate function

From: paul c <toledobythesea_at_oohay.ac>
Date: Wed, 18 Jul 2007 00:14:06 GMT
Message-ID: <iXcni.126549$xq1.72655@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 Tue Jul 17 2007 - 19:14:06 CDT