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@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 Tue Jul 17 2007 - 22:57:04 CDT