Stuck to select maximum row

I have a table with columns:

ID | FULLNAME | VALUE
01   Joseph     10
02   Sam        50
...  ...       ... 

I need to select row with maximum value, and show info like

FULLNAME | VALUE 

I tried using group function MAX(), but I can’t select fullname, because if I use it as a GROUP BY expression, it will select max in groups.

Other way, is to use WITH statement, order table by value desc, use

rank() OVER (PARTITION BY ID) AS max_id 

function and maximum value will be on max_id = 1, and then use

WHERE max_id = 1 

to remove other rows.

But I think there is a way to do this better and I can’t find one.

UPDATE:

A tricky solution to this problem is

SELECT *
FROM t t1
    LEFT JOIN t t2 ON t1.value<t2.value
WHERE t2.value IS NULL

Answer

The simplest way is to sort the data and pull one row:

select t.*
from t
order by value desc
fetch first 1 row only;

If you want ties, you can add with ties to the fetch first.

Another method is:

select t.*
from t
where t.value = (select max(t2.value) from t t2);

This can have very good performance with an index on value.