Getting Max row value in SQL

[Ali Reza]

hi all
i have a table in my data base that have the following schema:
mytable ( id , value)
like this:
id value
1 4
2 7
3 10
4 1
5 20

need to fetch 'id' of the row that have the max value in 'value' i try this query (and worked) :
SELECT R.id
FROM (SELECT id , MAX(value)
FROM mytable
GROUP BY id) as R(id,mymax)

my question : is there any better way to doing this work ? if so,how?

[wysota]

Hmmm...

"SELECT id, MAX(value) FROM mytable;"?

alternatively

"SELECT id FROM mytable ORDER BY value DESC LIMIT 1"

[Ali Reza]

thanks Mr wysota for replying.

please explain to me "LIMIT 1" what will do? i'm using Microsoft SQL Server 2008 (it seems the sql server not support this clause)

thanks

-- solved i must use TOP clause in SQL Server ! thanks again Mr Wysota !

[ChrisW67]

Neither TOP nor LIMIT is portable if that is a concern. You can do it with a sub-select, and this should work everywhere (until someone finds an SQL engine that doesn't).

Be careful, you could have several rows with the same maximum value. Which one would you want in that case?

> select id, value from test;
1|1
2|2
3|3
4|31
5|31
6|32
7|44
8|44
 
> select id from test where value = (select max(value) from test);
7
8
> select max(a) from test where b = (select max(b) from test);
8
> select min(a) from test where b = (select max(b) from test);
7

> select id, value from test;
1|1
2|2
3|3
4|31
5|31
6|32
7|44
8|44

> select id from test where value = (select max(value) from test);
7
8
> select max(a) from test where b = (select max(b) from test);
8
> select min(a) from test where b = (select max(b) from test);
7

To copy to clipboard, switch view to plain text mode 

For large row counts an index on value would be a good idea.