Re: Return rowid with max()

"music4" <music4_at_163.net> wrote
>
> Say I have a table with two columns: name, score. I want to get name of the
> highest score. I wrote following SQL statement:
>
> select name from tbl where score = (
> select max(score) from tbl)
>
> The statement includes two select, is there a way to use only one select to
> get the name of highest score's name?

Yes.. kind of. The above SQL would be a bad idea if score is not indexed.. Methinks that such a column would likely not be indexed anyway. This will result in a full table scan - after an already completed full table scan done by the inline view.

This SQL could be cheaper and faster:
SELECT name FROM tbl ORDER BY score DESC

And then you only deal (in the client code) with the 1st row returned and discard the rest (adding a FIRST_ROW hint could help?).

Alternatively, you can wrap the select as follows: SELECT * FROM (SELECT name FROM tbl ORDER BY score DESC) WHERE rownum = 1

IMO this is not much of an overhead (the outer SQL) as it simply adds a stop criteria to the output that is send to you.

I suggest looking at alternatives, doing an explain plan on each and only then deciding which one provides the best perfomance with minimal i/o impact.

There are many ways to roll your own in SQL.. :-)

--
Billy