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:

  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]>

Reply via email to