retrieving second most highest value from the table

Question

how do i retrieve the second highest value from the table

Answer 1

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

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

See 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

Answer 2

Maybe:

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

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

one solution would be like this:

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

Answer 7

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

Answer 8

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

Answer 9

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 & Sql Server

Answer 10

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

Answer 11

There can be many methods

Refer http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx