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:
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.

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:---

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

hope it works

SELECT max(value) FROM TABLE WHERE value NOT IN (SELECT max(value) FROM the TABLE);

or


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
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
SELECT max(sal) FROM emp WHERE sal< (SELECT max(sal) FROM emp)