ansaurus

Question

Finding record in mysql table that has the highest value

Answer 1

+3 A:

Try this:

SELECT *
FROM map
WHERE positionV = (SELECT MAX(positionV) FROM map)

Gumbo 2009-09-27 19:51:43

Wow. For all those years I've been writing sub-queries for selecting MAX, MIN, etc. I didn't know it also worked like this. Thanks!

Zed 2009-09-27 19:54:09

Hmm, It keeps throwing up an error when I try to use this, even when I try the query in phpmyadmin it says "#1111 - Invalid use of group function".

Stanni 2009-09-27 20:06:17

@Stanni: Try it with a sub-query.

Gumbo 2009-09-27 20:12:16

Nice one, it's working now, cheers.

Stanni 2009-09-27 20:14:16

Answer 2

+1 A:

SELECT * FROM map WHERE condition HAVING positionV = MAX(positionV)

Better to use having when doing aggregates.

2009-09-27 19:59:14

Why is it better?

rojoca 2009-09-27 20:27:38

@rojoca: You can't use aggregate functions in a WHERE clause, because the WHERE expressions are evaluated per row, not per group of rows.

Bill Karwin 2009-09-28 02:18:54

Answer 3

A:

wouldnt

SELECT * FROM `map` WHERE 1 ORDER BY `positionV` DESC LIMIT 1

be sufficient?

whats the resource weight of the various methods given, anyone know?

DBunting 2009-09-28 01:49:54

If the table has an index on `positionV`, all of these queries will be optimized to just find the row with the highest value in the index (i.e. the last element in an ascending-sort index) and return the contents of that row. 1 page fetch for the index, 1 page fetch for the table row, assuming neither are in cache.

dcrosta 2009-09-28 02:33:45

soSELECT * FROM map WHERE condition HAVING positionV = MAX(positionV)is more efficient?

DBunting 2009-09-28 02:57:13

ansaurus

tags:

views:

answers:

Finding record in mysql table that has the highest value

related questions