Re: SQL for aggregate group by

From: Robert Eden <rmeden_at_yahoo.com>
Date: Tue, 04 Nov 2003 03:33:19 GMT
Message-ID: <36Fpb.1510$8J7.1038@newssvr22.news.prodigy.com>

"Reinhard Wahl" <Reinhard_nospam.Wahl_at_gmx.de> wrote in message news:bo57vm$182lns$1_at_ID-> SELECT tn.item, tn.time, tn.qty
> FROM table_name tn
> , ( SELECT item, max(qty) maxqty
> FROM table_name GROUP BY item) v
> WHERE tn.item = v.item
> AND tn.qty = v.maxqty;

Thanks! I've used nested queries before, but never inline views. It's much more efficient than I would have thought, it runs much faster than a similar query I was able to make work using nested queries.

I guess I read the SQL Ref Manual section on aggregates so many times I tried much too hard to make that work.

Thanks again, Reinhard!

Robert

note to Daniel, this wasn't for a class, but I did learn something today! Received on Mon Nov 03 2003 - 21:33:19 CST