Retrieving the second most highest value from a table

How do I retrieve the开发者_JS百科 second highest value from a table?

---

select max(val) from table where val < (select max(val) form table) 

---

In MySQL you could for instance use LIMIT 1, 1:

SELECT col FROM tbl ORDER BY col DESC LIMIT 1, 1

See the MySQL reference manual: SELECT Syntax).

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

---

select top 2 field_name from table_name order by field_name desc limit 1

---

SELECT E.lastname, E.salary FROM employees E
WHERE 2 = (SELECT COUNT(*) FROM employess E2
            WHERE E2.salary > E.salary)

Taken from here
This works in almost all Dbs

---

Select Top 1 sq.ColumnToSelect
From
(Select Top 2 ColumnToSelect
From MyTable
Order by ColumnToSelect Desc
)sq
Order by sq.ColumnToSelect asc

---

Cool, this is almost like Code Golf.

Microsoft SQL Server 2005 and higher:

SELECT *
FROM (
    SELECT 
        *,
        row_number() OVER (ORDER BY var DESC) AS ranking
    FROM table
) AS q
WHERE ranking = 2

---

Try this

SELECT * FROM 
(SELECT empno, deptno, sal,
DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC NULLS LAST) DENSE_RANK
FROM emp)
WHERE DENSE_RANK = 2;

This works in both Oracle and SQL Server.

---

Try this

SELECT TOP 1 Column FROM Table WHERE Column < (SELECT MAX(Column) FROM Table) 
ORDER BY Column DESC

SELECT TOP 1 Column FROM (SELECT TOP <n> Column FROM Table ORDER BY Column DESC) 

ORDER BY ASC

change the n to get the value of any position

---

Maybe:

SELECT * FROM table ORDER BY value DESC LIMIT 1, 1

---

one solution would be like this:

SELECT var FROM table ORDER BY var DESC LIMIT 1,1