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.
This question is tagged with
~ Asked on 2008-08-28 12:39:53
SELECT MAX( col ) FROM table WHERE col < ( SELECT MAX( col ) FROM table )
~ Answered on 2008-08-28 12:43:40
SELECT MAX(col) FROM table WHERE col NOT IN (SELECT MAX(col) FROM table);
~ Answered on 2011-09-09 13:13:56
In T-Sql there are two ways:
--filter out the max select max( col ) from [table] where col < ( select max( col ) from [table] ) --sort top two then bottom one select top 1 col from ( select top 2 col from [table] order by col) topTwo order by col desc
In Microsoft SQL the first way is twice as fast as the second, even if the column in question is clustered.
This is because the sort operation is relatively slow compared to the table or index scan that the
max aggregation uses.
Alternatively, in Microsoft SQL 2005 and above you can use the
select col from ( select ROW_NUMBER() over (order by col asc) as 'rowNum', col from [table] ) withRowNum where rowNum = 2
~ Answered on 2008-08-28 13:19:30
I see both some SQL Server specific and some MySQL specific solutions here, so you might want to clarify which database you need. Though if I had to guess I'd say SQL Server since this is trivial in MySQL.
I also see some solutions that won't work because they fail to take into account the possibility for duplicates, so be careful which ones you accept. Finally, I see a few that will work but that will make two complete scans of the table. You want to make sure the 2nd scan is only looking at 2 values.
SQL Server (pre-2012):
SELECT MIN([column]) AS [column] FROM ( SELECT TOP 2 [column] FROM [Table] GROUP BY [column] ORDER BY [column] DESC ) a
SELECT `column` FROM `table` GROUP BY `column` ORDER BY `column` DESC LIMIT 1,1
SQL Server 2012 now supports a much cleaner (and standard) OFFSET/FETCH syntax:
SELECT TOP 2 [column] FROM [Table] GROUP BY [column] ORDER BY [column] DESC OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;
~ Answered on 2008-08-28 14:24:04
I suppose you can do something like:
SELECT * FROM Table ORDER BY NumericalColumn DESC LIMIT 1 OFFSET 1
SELECT * FROM Table ORDER BY NumericalColumn DESC LIMIT (1, 1)
depending on your database server. Hint: SQL Server doesn't do LIMIT.
~ Answered on 2008-08-28 12:43:25
The easiest would be to get the second value from this result set in the application:
SELECT DISTINCT value FROM Table ORDER BY value DESC LIMIT 2
But if you must select the second value using SQL, how about:
SELECT MIN(value) FROM (SELECT DISTINCT value FROM Table ORDER BY value DESC LIMIT 2) AS t
~ Answered on 2008-08-28 12:42:49
you can find the second largest value of column by using the following query
SELECT * FROM TableName a WHERE 2 = (SELECT count(DISTINCT(b.ColumnName)) FROM TableName b WHERE a.ColumnName <= b.ColumnName);
you can find more details on the following link
~ Answered on 2013-01-23 06:15:57
SELECT * FROM [Users] order by UserId desc OFFSET 1 ROW FETCH NEXT 1 ROW ONLY;
SELECT * FROM Users order by UserId desc LIMIT 1 OFFSET 1
No need of sub queries ... just skip one row and select second rows after order by descending
~ Answered on 2017-07-12 05:34:39
A very simple query to find the second largest value
SELECT `Column` FROM `Table` ORDER BY `Column` DESC LIMIT 1,1;
~ Answered on 2012-10-25 11:01:20
This is very simple code, you can try this :-
ex : Table name = test
salary 1000 1500 1450 7500
MSSQL Code to get 2nd largest value
select salary from test order by salary desc offset 1 rows fetch next 1 rows only;
here 'offset 1 rows' means 2nd row of table and 'fetch next 1 rows only' is for show only that 1 row. if you dont use 'fetch next 1 rows only' then it shows all the rows from the second row.
~ Answered on 2017-06-23 07:46:07
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
~ Answered on 2016-07-29 18:52:25
SELECT MAX(Salary) FROM Employee WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee )
This query will return the maximum salary, from the result - which not contains maximum salary from overall table.
~ Answered on 2015-05-09 02:40:18
Simplest of all
select sal from salary order by sal desc limit 1 offset 1
~ Answered on 2015-09-10 08:08:03
select * from (select ROW_NUMBER() over (Order by Col_x desc) as Row, Col_1 from table_1)as table_new tn inner join table_1 t1 on tn.col_1 = t1.col_1 where row = 2
Hope this help to get the value for any row.....
~ Answered on 2012-06-27 19:04:40
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
SELECT amount FROM salary GROUP by amount ORDER BY amount DESC LIMIT 1 , 1
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
~ Answered on 2016-12-06 16:03:12
select max(column_name) from table_name where column_name not in (select max(column_name) from table_name);
not in is a condition that exclude the highest value of column_name.
Reference : programmer interview
~ Answered on 2017-09-25 14:34:58
Tom, believe this will fail when there is more than one value returned in
select max([COLUMN_NAME]) from [TABLE_NAME] section. i.e. where there are more than 2 values in the data set.
Slight modification to your query will work -
select max([COLUMN_NAME]) from [TABLE_NAME] where [COLUMN_NAME] **IN** ( select max([COLUMN_NAME]) from [TABLE_NAME] )
~ Answered on 2010-11-25 16:53:19
select max(COL_NAME) from TABLE_NAME where COL_NAME in (select COL_NAME from TABLE_NAME where COL_NAME < (select max(COL_NAME) from TABLE_NAME));
subquery returns all values other than the largest. select the max value from the returned list.
~ Answered on 2010-11-25 16:58:29
It is the most esiest way:
SELECT Column name FROM Table name ORDER BY Column name DESC LIMIT 1,1
~ Answered on 2013-08-23 18:28:41
select age from student group by id having age<(select max(age) from student)order by age limit 1
~ Answered on 2015-02-28 18:16:43
SELECT MAX(sal) FROM emp WHERE sal NOT IN (SELECT top 3 sal FROM emp order by sal desc )
this will return the third highest sal of emp table
~ Answered on 2017-03-10 11:43:16
SELECT * FROM table WHERE column < (SELECT max(columnq) FROM table) ORDER BY column DESC LIMIT 1
~ Answered on 2012-10-23 16:13:39
select min(sal) from emp where sal in (select TOP 2 (sal) from emp order by sal desc)
sal is col name
emp is table name
~ Answered on 2011-08-06 13:17:27
select col_name from ( select dense_rank() over (order by col_name desc) as 'rank', col_name from table_name ) withrank where rank = 2
~ Answered on 2012-03-30 08:40:24
Microsoft SQL Server - Using Two TOPs for the N-th highest value (aliased sub-query).
To solve for the 2nd highest:
SELECT TOP 1 q.* FROM (SELECT TOP 2 column_name FROM table_name ORDER BY column_name DESC) as q ORDER BY column_name ASC;
Uses TOP twice, but requires an aliased sub-query. Essentially, the inner query takes the greatest 2 values in descending order, then the outer query flips in ascending order so that 2nd highest is now on top. The SELECT statement returns this top.
To solve for the n-th highest value modify the sub-query TOP value. For example:
SELECT TOP 1 q.* FROM (SELECT TOP 5 column_name FROM table_name ORDER BY column_name DESC) as q ORDER BY column_name;
Would return the 5th highest value.
~ Answered on 2016-05-23 20:11:48
Very Simple. The distinct keyword will take care of duplicates as well.
SELECT distinct SupplierID FROM [Products] order by SupplierID desc limit 1 offset 1
~ Answered on 2017-04-17 13:42:47
select extension from [dbo].[Employees] order by extension desc offset 2 rows fetch next 1 rows only
~ Answered on 2016-09-03 06:47:26
You can find nth highest value using the following query.
select top 1 UnitPrice from (select distinct top n UnitPrice from [Order Details] order by UnitPrice desc) as Result order by UnitPrice asc
Here, the value of n will be 1 (for the highest number), 2 (for the second highest number), 3 (for the third highest number)...
~ Answered on 2018-02-24 15:46:40
SELECT * FROM `employee` WHERE employee_salary = (SELECT employee_salary FROM`employee` GROUP BY employee_salary ORDER BY employee_salary DESC LIMIT 1,1)
~ Answered on 2017-09-25 11:32:40
At first make a dummy table without max salary then query max value from dummy table
SELECT max(salary) from (Select * FROM emp WHERE salary<> (SELECT MAX(salary) from emp)) temp
~ Answered on 2018-11-15 20:04:51
The easiest way to get second last row from a SQL table is to use
DESC and set
SELECT * from `TableName` ORDER BY `ColumnName` DESC LIMIT 1,1
~ Answered on 2017-06-06 07:14:03
SELECT * FROM EMP WHERE salary= (SELECT MAX(salary) FROM EMP WHERE salary != (SELECT MAX(salary) FROM EMP) );
~ Answered on 2013-05-13 07:17:17
select * from emp e where 3>=(select count(distinct salary) from emp where s.salary<=salary)
This query selects the maximum three salaries. If two emp get the same salary this does not affect the query.
~ Answered on 2009-07-09 05:03:36
Use this query.
SELECT MAX( colname ) FROM Tablename where colname < ( SELECT MAX( colname ) FROM Tablename)
~ Answered on 2014-03-24 10:44:48
select * from [table] where (column)=(select max(column)from [table] where column < (select max(column)from [table]))
~ Answered on 2013-11-26 07:43:51
select a.* ,b.* from (select * from (select ROW_NUMBER() OVER(ORDER BY fc_amount desc) SrNo1, fc_amount as amount1 From entry group by fc_amount) tbl where tbl.SrNo1 = 2) a , (select * from (select ROW_NUMBER() OVER(ORDER BY fc_amount asc) SrNo2, fc_amount as amount2 From entry group by fc_amount) tbl where tbl.SrNo2 =2) b
~ Answered on 2013-09-12 11:40:58
Using a correlated query:
Select * from x x1 where 1 = (select count(*) from x where x1.a < a)
~ Answered on 2009-09-14 20:21:11
select top 1 MyIntColumn from MyTable where MyIntColumn <> (select top 1 MyIntColumn from MyTable order by MyIntColumn desc) order by MyIntColumn desc
~ Answered on 2008-08-28 12:44:49
This works in MS SQL:
select max([COLUMN_NAME]) from [TABLE_NAME] where [COLUMN_NAME] < ( select max([COLUMN_NAME]) from [TABLE_NAME] )
~ Answered on 2008-08-28 12:45:03
Something like this? I haven't tested it, though:
select top 1 x from ( select top 2 distinct x from y order by x desc ) z order by x
~ Answered on 2008-08-28 12:45:30
Sybase SQL Anywhere supports:
SELECT TOP 1 START AT 2 value from table ORDER BY value
~ Answered on 2008-08-28 13:36:14
select score from table where score = (select max(score)-1 from table)
~ Answered on 2016-03-04 03:57:52
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
4 u can find the 3rd, 4th and 5th highest respectively.
~ Answered on 2013-03-25 06:37:38
We can also make use of order by and top 1 element as follows:
Select top 1 col_name from table_name where col_name < (Select top 1 col_name from table_name order by col_name desc) order by col_name desc
~ Answered on 2013-04-23 10:54:26
select MAX(salary) as SecondMax from test where salary !=(select MAX(salary) from test)
~ Answered on 2014-04-21 12:20:18
This is an another way to find the second largest value of a column.Consider the table 'Student' and column 'Age'.Then the query is,
select top 1 Age from Student where Age in(select distinct top 2 Age from Student order by Age desc) order by Age asc
~ Answered on 2013-08-15 08:07:54