Problem: Write a SQL query to find the 2nd largest value in a column in a table.
In the 1st example find the 2nd largest value in column “Income” and in the 2nd one find the 2nd largest value in “Cost”.
Input: Table name- Employee +------+--------+ | Name | Income | +------+--------+ | abc | 4000 | | xyz | 4752 | | qwe | 6579 | +------+--------+ Output: 4752 Input: Table name- price_list +-------------+--------+ | Item | Cost | +-------------+--------+ | Apple | 150 | | Banana | 175 | | Mango | 200 | | Pineapple | 180 | +-------------+--------+ Output: 180
SELECT MAX (column_name) FROM table_name WHERE column_name NOT IN (SELECT Max (column_name) FROM table_name);
First we selected the max from that column in the table then we searched for the max value again in that column with excluding the max value which has already been found, so it results in the 2nd maximum value.
SELECT MAX (Income) FROM Employee WHERE Salary NOT IN (SELECT Max (Income) FROM Employee);
SELECT MAX (Cost) FROM price_list WHERE Salary NOT IN (SELECT Max (Cost) FROM price_list);
SELECT column_name FROM table_name e WHERE 2 = (SELECT COUNT (DISTINCT column_name) FROM table_name p WHERE e.column_name<=p.column_name)
This is a nested sub query which is a generic SQL query to print the Nth largest value in column. For each record processed by outer query, inner query will be executed and will return how many records has records has value less than the current value. If you are looking for second highest value then your query will stop as soon as inner query will return 2.
SELECT Income FROM Employee e WHERE 2=(SELECT COUNT(DISTINCT Income) FROM Employee p WHERE e.Income<=p.Income)
SELECT Cost FROM price_list e WHERE 2=(SELECT COUNT(DISTINCT Cost) FROM price_list p WHERE e.Cost<=p.Cost)
Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.
- SQL query to find unique column values from table
- SQL | Query to select NAME from table using different options
- SQL query to find second highest salary?
- How to find Nth highest salary from a table
- SQL | SELECT Query
- Structured Query Language (SQL)
- PHP | MySQL UPDATE Query
- PHP | MySQL Select Query
- PHP | MySQL Delete Query
- SQL | Query Processing
- Join operation Vs Nested query in DBMS
- What are the Best Ways to Write a SQL Query?
- Difference between Structured Query Language (SQL) and Transact-SQL (T-SQL)
- Features of Structured Query Language (SQL)
- SQL query using COUNT and HAVING clause
- SQL Query Complexity
- Allow only alphabets in column in SQL Server
- How to print duplicate rows in a table?
- SQL | Create Table Extension
- PHP | MySQL ( Creating Table )
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to firstname.lastname@example.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.