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_at_news.aliant.net>
>> "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?
Date: Tue, 17 Jul 2007 22:09:42 -0300
Message-ID: <469d6832$0$8831$9a566e8b_at_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 Wed Jul 18 2007 - 03:09:42 CEST