Re: question using aggregate function
Date: Wed, 18 Jul 2007 03:28:27 GMT
Message-ID: <vNfni.127954$1i1.117614_at_pd7urf3no>
Bob Badour wrote:
> 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? > ...