SQL select maximal value from Table

Question

On one of my interviews I was asked how it's possible to select maximal value from DB without keyword MAX and TOP.

My answer was:

select Table.Value 
from Table 
where Table.Value >= all( select Table.Value from Table) 

But this wasn't the right one. The interviewer said that I should do it only with one select.

Any ideas ?

Thank you ;)

Answer 1

How about;

select -min(-fld) from table

Less efficient & uglier Woops missed the single select restriction

select distinct Value from Table T
  where not exists (select Value from Table where Value > T.Value)

Answer 2

SELECT t1.Value
FROM atable t1
 LEFT JOIN atable t2 ON t1.Value < t2.Value
WHERE t2.ID IS NULL

Answer 3

One suggestion (if you use MySQL, I put limit also):

SELECT table.value FROM table ORDER BY table.value DESC LIMIT 1;

Answer 4

SET ROWCOUNT 1

SELECT number 
FROM master..spt_values
ORDER BY number DESC

Answer 5

order the result set by the key, descending, nulls last. That makes the row with the max, first.

select the first row, and you are done.