开发者

SQL select maximal value from Table

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.Val开发者_Go百科ue >= 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 ;)


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)


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


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

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


SET ROWCOUNT 1

SELECT number 
FROM master..spt_values
ORDER BY number DESC


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜