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.
Bookmarks