Re: question using aggregate function

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Tue, 17 Jul 2007 22:09:42 -0300
Message-ID: <469d6832$0$8831$9a566e8b@news.aliant.net>

paul c wrote:

> 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.

Are you suggesting he would have found ALL or ANY or EXISTS foreign concepts?

I wonder, though: Is a scalar subquery a correlated subquery? I never did understand the difference between a correlated subquery and an uncorrelated subquery. Received on Tue Jul 17 2007 - 20:09:42 CDT