On Mon, May 23, 2005 at 08:00:22AM +0200, Ludvig Strigeus wrote: > Why doesn't SQL provide a utility function: maxv > > Then you could (almost) write it like this: > SELECT maxv(sb, playerid) FROM batting WHERE playerid IN > (SELECT player FROM fielding WHERE pos='3B' AND lgid='NL')); > > The semantics of maxv(arg, value) would be that it finds the maximum > of arg, but instead of returning arg, it returns value for the record > with the maximum arg.
I always thought of that feature as "look-aside", as in, "Find the max of foo, but don't give me that, instead look aside and return me the corresponding value of bar next to it instead." I've wanted that feature for years, and then not long ago discovered, buried in the Oracle docs, that it exists in Oracle 9i and up! Since you want the playerid with the most sb (stolen bases), in Oracle 9.2.x it would be something like this: select max(playerid) keep (dense_rank last order by sb) as top_stealer from batting I don't know whether the SQL Standard includes any such feature, but it sure is fabulously useful. With a simple queries like these you can make it work without that feature, but when you're in the middle of a complex query that's already a page or two long, without this sort of feature, there's often no sane way to do it at all. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/
