Re: question using aggregate function
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Wed, 18 Jul 2007 09:17:04 -0300
Message-ID: <469e049b$0$8865$9a566e8b_at_news.aliant.net>
>> 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?
>>> ...
>>
>> i'm pretty sure that was a rhetorical question, but i'll sort-of bite
>> anyway. I wonder what the heck do people who learned English as a
>> second language think of these terms (which seem fundamental to me,
>> not because I'm objective but because I'm used to them, so my thoughts
>> may well be distorted by my upbringing and haven't yet learned how to
>> compare them to the other themes that Codd involved in his idea,
>> information principle and so forth).
>>
>> p
Date: Wed, 18 Jul 2007 09:17:04 -0300
Message-ID: <469e049b$0$8865$9a566e8b_at_news.aliant.net>
paul c wrote:
> paul c wrote: >
>> 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?
>>> ...
>>
>> i'm pretty sure that was a rhetorical question, but i'll sort-of bite
>> anyway. I wonder what the heck do people who learned English as a
>> second language think of these terms (which seem fundamental to me,
>> not because I'm objective but because I'm used to them, so my thoughts
>> may well be distorted by my upbringing and haven't yet learned how to
>> compare them to the other themes that Codd involved in his idea,
>> information principle and so forth).
>>
>> p
> > (I wasn't suggesting that one's first language is the only mental tool, > have met people from many other countries who could express the ideas in > a formal notation, but as we see with talk about identity and views, > the formal methods usually allow several different human interpretations.)
English pollutes a lot of mathematics. Whether one uses and upside-down A or a backward E for ALL and EXISTS, I don't think it changes things much.
Quantifiers and sub-queries were certainly in Codd's mind. But are those correlated? I honestly do not know what makes a subquery correlated versus uncorrelated. Received on Wed Jul 18 2007 - 14:17:04 CEST