Problems on LIMIT and their solution in MySQL

Easy Solution Web : MySQL Tutorials

We have a table structure with table name EMP as showing in below image

MySQL : Employee Table Structure
MySQL : Employee Table Structure

 

1. Write A Query to find 2nd Highest Salary in MySQL?

Ans.SELECT SAL FROM EMP ORDER BY SAL DESC LIMIT 2,1;

 

2. Write A Query to find Nth Highest Salary in MySQL?

Ans.SELECT SAL FROM EMP ORDER BY SAL DESC LIMIT (Nth-1), 1;

 

3. Write A Query to find Nth Lowest Salary in MySQL?

Ans.SELECT SAL FROM EMP ORDER BY SAL ASC LIMIT (Nth-1), 1;

 

These are the three Most important and Most Asked Questions in an interview if you are preparing for Interview these concepts will be helpful to you. In which many failed to answer these Questions even the experienced one.

Here we use Limit to answer these Question.

Limit is used to select particular no of rows from the table.

For example:-

MySQL -> SELECT * FROM EMP LIMIT 10;

It will select rows which are showing in the above image.

MySQL -> SELECT * FROM EMP LIMIT 1;

It will select row 1 having EMPNO of 7839.

We can use LIMIT in two ways in MySQL :

  1. LIMIT N
  2. LIMIT M,N

LIMIT N will select 0 to N rows from the total rows returned by the Query.

LIMIT M,N will select M+1 to M+N rows from the total rows returned by Query.

Are there any other ways to resolve the above the Queries.

Yes. There is

We can solve above problem using sub-query.

1. Write A Query to find 2nd Highest Salary in MySQL?

Ans. SELECT * FROM (SELECT SAL FROM EMP ORDER BY SAL DESC LIMIT 2) AS T1 ORDER BY ASC LIMIT 1;

 

2. Write A Query to find Nth Highest Salary in MySQL?

Ans.SELECT * FROM (SELECT SAL FROM EMP ORDER BY SAL DESC LIMIT N) AS T1 ORDER BY ASC LIMIT 1;

 

3. Write A Query to find Nth Lowest Salary in MySQL?

Ans.SELECT * FROM (SELECT SAL FROM EMP ORDER BY SAL ASC LIMIT N) AS T1 ORDER BY DESC LIMIT 1;

 

Another useful Application of LIMIT in any application is WHEN you need PAGINATION.

LIMIT M,N

Using this LIMIT we can implement pagination in our Application.

For example, if we use above table EMP and we want 3 row to show in each page in our application.

MySQL -> SELECT * FROM EMP LIMIT  0 , 3;  /* Rows to Show on First Page */
MySQL -> SELECT * FROM EMP LIMIT  3 , 3; /* Rows to Show on Second Page */
MySQL -> SELECT * FROM EMP LIMIT  6 , 3; /* Rows to Show on Third Page */