What is the simplest SQL Query to find the second

Question

What is the simplest SQL query to find the second largest integer value in a specific column?

There are maybe duplicate values in the column.

Answer 1

Simplest of all

select sal from salary order by sal desc limit 1 offset 1

Answer 2

Old question I know, but this gave me a better exec plan:

 SELECT TOP 1 LEAD(MAX (column)) OVER (ORDER BY column desc)
 FROM TABLE 
 GROUP BY column

Answer 3

It is the most esiest way:

SELECT
      Column name
FROM
      Table name 
ORDER BY 
      Column name DESC
LIMIT 1,1

Answer 4

As you mentioned duplicate values . In such case you may use DISTINCT and GROUP BY to find out second highest value

Here is a table

salary

:

GROUP BY

SELECT  amount FROM  salary 
GROUP by amount
ORDER BY  amount DESC 
LIMIT 1 , 1

DISTINCT

SELECT DISTINCT amount
FROM  salary 
ORDER BY  amount DESC 
LIMIT 1 , 1

First portion of LIMIT = starting index

Second portion of LIMIT = how many value

Answer 5

Query to find the 2nd highest number in a row-

select Top 1 (salary) from XYZ
where Salary not in (select distinct TOP 1(salary) from XYZ order by Salary desc)
ORDER BY Salary DESC

By changing the highlighted Top 1 to TOP 2, 3 or 4 u can find the 3rd, 4th and 5th highest respectively.

Answer 6

SELECT 
    * 
FROM 
    table 
WHERE 
    column < (SELECT max(columnq) FROM table) 
ORDER BY 
    column DESC LIMIT 1