How to find second highest value of a column in a table? | SQL?

Dialect: Oracle

Dialect: SQL 92

SQL:

select score,player from scores where score=(select max(score) from scores where score< (select max(score) from scores));

The above finds the second highest score in the scores table and the name of the player.
Reading from right to left:
1. The first select finds the highest score.
2. The second select find the highest from the rest.
3. The last select finds the name of the player.

Hope this helps.

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));

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.

Dialect: SQL 92

SQL:

SELECT score FROM scores WHERE 1 ORDER BY score DESC LIMIT 1,1

Dialect: SQL 92

SQL:

SELECT score FROM scores WHERE 1 ORDER BY score DESC LIMIT 1,1

Dear Anonymous, I think the above is not supported by SQL 92.

consider this code:---

SQL:

SELECT MAX(OFFICE_LOCATION_ID) FROM table

WHERE OFFICE_LOCATION_ID < (SELECT MAX(OFFICE_LOCATION_ID) FROM  table)

hope it works

Dialect: SQL 92

SQL:

SELECT max(score ) FROM scores WHERE score not in (SELECT max(score) 

FROM scores)

SQL:

select max(value) from table where value not in (select max(value) from the table);

or

SQL:

select level,max(value) from table where level=2 connect proir by value>value group by level;

vivek

--find 2 or 3 lowest value in record

SQL:

select min(empmarks )

from emp

where  empmarks in (select top 3 empmarks from emp

        order by empmarks DESC)

--by kamal pandey for sql server only..

This is sekhar

SQL:

select max(sal) from emp where sal< (select max(sal) from emp)