SEO

#1 (**permalink**) 04-29-2008, 08:27 PM

Folks, I want to SELECT the single highest value of x. What does
"Order by" really order, the full set of table rows before the single
selection is returned, or only the returned row? (which wd clearly be
useless in this case.)

Or is there better SQL? Thanks.

AS

#2 (**permalink**) 04-29-2008, 08:27 PM

ashore wrote:
> Folks, I want to SELECT the single highest value of x. What does
> "Order by" really order, the full set of table rows before the single
> selection is returned, or only the returned row? (which wd clearly be
> useless in this case.)
>
> Or is there better SQL? Thanks.
>
> AS
>

SELECT MAX(x) FROM mytable;

You can also add LIMIT 1 if you have 2 or more rows with the same max value.

ORDER BY must sort before LIMIT so the server knows which rows should be
affected by LIMIT.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

#3 (**permalink**) 04-29-2008, 08:27 PM

Jerry Stuckle schreef:
> ashore wrote:
>> Folks, I want to SELECT the single highest value of x. What does
>> "Order by" really order, the full set of table rows before the single
>> selection is returned, or only the returned row? (which wd clearly be
>> useless in this case.)
>>
>> Or is there better SQL? Thanks.
>>
>> AS
>>
>
> SELECT MAX(x) FROM mytable;
>
> You can also add LIMIT 1 if you have 2 or more rows with the same max
> value.
>
> ORDER BY must sort before LIMIT so the server knows which rows should be
> affected by LIMIT.
>

mysql> select distinct t from tijd;
+----------+
| t |
+----------+
| 15:00:00 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from tijd;
+----------+
| count(*) |
+----------+
| 66 |
+----------+
1 row in set (0.00 sec)

mysql> select max(t) from tijd;
+----------+
| max(t) |
+----------+
| 15:00:00 |
+----------+
1 row in set (0.00 sec)

so, even when you have multiple rows with same MAX value, you do not
have to add 'LIMIT 1'...

--
Luuk

#4 (**permalink**) 04-29-2008, 08:28 PM

Jerry Stuckle wrote:
> ashore wrote:
>> Folks, I want to SELECT the single highest value of x. What does
>> "Order by" really order, the full set of table rows before the single
>> selection is returned, or only the returned row? (which wd clearly be
>> useless in this case.)
>>
>> Or is there better SQL? Thanks.
>>
>> AS
>>
>
> SELECT MAX(x) FROM mytable;
>
> You can also add LIMIT 1 if you have 2 or more rows with the same max
> value.
>
> ORDER BY must sort before LIMIT so the server knows which rows should be
> affected by LIMIT.

The query engine is smarter than that. If the ORDER BY clause
is based on a key, then SELECT ... ORDER BY ... LIMIT 1 can
be optimized and is a very cheap operation. Otherwise, it
takes a pass through the whole database. See
"http://dev.mysql.com/doc/refman/5.0/en/limit-optimization.html"
LIMIT N is handled differently for small values of N.

SELECT MAX() is optimized, too, but due to a bug, optimization
may not occur in MySQL before 5.0.18. See "http://bugs.mysql.com/bug.php?id=16016".

Of course, if the field isn't indexed, a pass through the whole
database is unavoidable.

John Nagle

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

"order by" And "limit = 1"