Query to find 2nd largest value in a column in Table

In this article, we will learn how to find the 2nd largest value in a column of a table. It's a pretty common task when working with databases, especially when you need to analyze data. We will explore efficient methods to find the second-largest value using SQL queries, with step-by-step explanations and examples for better understanding.

Steps to Retrieve 2^nd Largest Value in a Column

Here is the step-by-step explanation of getting 2^nd largest value in a column in a table.

Step 1: Check Database

First, check the database where the table exists. If the database does not exist, run the following query to create it.

CREATE DATABASE school;

To change the database, use this.

use <database_name>;

Step 2: Check Table

Ensure that the table from which you want to retrieve the column names exists. If the table does not exist, create it using the following query.

CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT NOT NULL,
class VARCHAR(20) NOT NULL,
grade CHAR(1) NOT NULL
);

Step 3 : Insert Data into Table

You can insert data into the table using the following query.

INSERT INTO students (id, name, age, class, grade) VALUES
(1, 'Aarav Sharma', 14, '9th Grade', 'A'),
(2, 'Ishita Singh', 15, '10th Grade', 'B'),
(3, 'Rohan Verma', 16, '11th Grade', 'C'),
(4, 'Diya Gupta', 17, '12th Grade', 'A'),
(5, 'Kavya Rao', 13, '8th Grade', 'B'),
(6, 'Aditya Patel', 15, '10th Grade', 'A');

If you are inserting data in the same column order as defined in the table, you can omit the column names (id, name, age, class, grade) and directly provide the values. Here is an example query to insert a single row into the table, which can also be used for multiple entries by adding more rows:

INSERT INTO students VALUES (1, 'Aarav Sharma', 14, '9th Grade', 'A');

For multiple rows, use this format:

INSERT INTO students VALUES
(1, 'Aarav Sharma', 14, '9th Grade', 'A'),
(2, 'Ishita Singh', 15, '10th Grade', 'B'),
(3, 'Rohan Verma', 16, '11th Grade', 'C');

Step 4 : View Table Data

To see the data inside table, run the following query.

SELECT * FROM students;

Output

id	name	age	class	grade
1	Aarav Sharma	14	9th Grade	A
2	Ishita Singh	15	10th Grade	B
3	Rohan Verma	16	11th Grade	C
4	Diya Gupta	17	12th Grade	A
5	Kavya Rao	13	8th Grade	B
6	Aditya Patel	15	10th Grade	A

Step 5 : methods to Get 2^nd largest value in column

These are some methods that you can follow to find the 2^nd largest value in a column:

1. Using MAX Aggregation Function

This method involves using the `MAX()` function twice?once to find the largest value and then to exclude it while finding the second largest value. Here is the query:

Example

SELECT MAX(age) FROM students WHERE age NOT IN (SELECT MAX(age) FROM students);

Output

MAX(age)
16

2. Using `ORDER BY` and `LIMIT` with Exclusion

This method uses `ORDER BY` to sort the column in descending order and excludes the largest value using a subquery. The second largest value can be retrieved using `LIMIT`. `LIMIT` is used to restrict the number of records. The syntax is:

Example

SELECT age FROM students WHERE age != (SELECT MAX(age) FROM students) ORDER BY age DESC LIMIT 1;

Output

age
16

3. Using `ORDER BY` and Offset with `LIMIT`

This method sorts the column in descending order and uses `LIMIT` with an offset to directly retrieve the second largest value. The syntax is:

Here is the Syntax of LIMIT.

NOTE

Syntax : LIMIT <offset>, <row_count>;

offset : the number of rows to skip before starting to return rows.

row_count : the number of rows to return after skipping the offset.

Example

SELECT age FROM students ORDER BY age DESC LIMIT 1, 1;

Output

age
16