View Single Post
  #3 (permalink)  
Old 08-29-2006, 07:59 AM
Dimitar
 
Posts: n/a
Default ANY answer. Re: How to find second highest value of a column in a table? | SQL?

Consider the following data:
score player
#1 100 Alex
#2 100 Bob
#3 98 Toto
...
#9 20 Lilirtyry

My previous proposal:
[sql]
SELECT score,player FROM scores WHERE score=(SELECT max(score) FROM scores WHERE score< (SELECT max(score) FROM scores));
[/sql]
would select row #3 as having the second highest score, because 98 is the second highest value present in the table.
If this is not the correct result, as is the case with the example I have chosen, you need to define additional criteria to differentiate between rows #1 and #2, e.g. the older player wins, and modify the statement accordingly.
Edit/Delete Message Reply With Quote