Re: question using aggregate function

On Jul 18, 6:17 am, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
> paul c wrote:
> > paul c wrote:
>
> >> Bob Badour wrote:
>
> >>> paul c wrote:
>
> >>>> David Portas wrote:
>
> >>>>> "Mia" <nos..._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.- Hide quoted text -
>
> - Show quoted text -

Here's the distinction I see between correlated and non-correlated subqueries, although it's a view influenced by coming from a SQL (specifically MS's T-SQL) background originally:

• Subquery, but not correlated SELECT DISTINCT Supplier_ID FROM Orders WHERE Order_Date = (SELECT MAX(Order_Date) FROM Orders);
• Correlated subquery... SELECT Order_Id , ( SELECT MAX(Qty) FROM OrderLineItems AS oli WHERE oli.Order_ID = o.Order_ID -- Subquery references an attribute of the "outer" query's relation = correlated. ) AS [LargestItemQty] FROM Orders AS o

TroyK Received on Thu Jul 19 2007 - 16:38:21 CEST