Re: question using aggregate function
From: Jonathan Leffler <jleffler_at_earthlink.net>
Date: Wed, 18 Jul 2007 03:57:04 GMT
Message-ID: <kcgni.8420$tj6.3260_at_newsread4.news.pas.earthlink.net>
>> 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?
>
> Have you thought about using a correlated subquery?
Date: Wed, 18 Jul 2007 03:57:04 GMT
Message-ID: <kcgni.8420$tj6.3260_at_newsread4.news.pas.earthlink.net>
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?
>
> Have you thought about using a correlated subquery?
No - I haven't thought about using a correlated sub-query? Why complicate things?
SELECT DISTINCT Supplier_ID
FROM Orders
WHERE Order_Date = (SELECT MAX(Order_Date) FROM Orders);
Be prepared for it to return more than one row if more than one supplier places (or receives) an order on the same day.
-- Jonathan Leffler #include <disclaimer.h> Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com Guardian of DBD::Informix v2007.0226 -- http://dbi.perl.org/Received on Wed Jul 18 2007 - 05:57:04 CEST