Problems on LIMIT and their solution in MySQL

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 */

 

 

Concepts of insert and limit in MySQL

Here we will discuss some useful concepts on insert statement and limits, which will be proved to be a boon to your development career if you don’t know these yet.

Let’s name them first

  1. On duplicate update or insert update.
  2. Insert into select.
  3. Insert Ignore.
  4. Use of Limit.

On duplicate update

If you want to insert in the table and on the conflict of the primary key or unique index it will update the conflicting row instead of inserting that row.

Syntax :

insert into table1 set column1 = a, column2 = b on dulplicate update column2 = c;

Now here, this insert statement may look different what you have seen earlier. This insert statement trying to insert a row in table1 with the value of a and b into column column1 and column2 respectively.

Let’s understand this statement into deep:

 

For example:-  here column1 is defined as the primary key in table1.

Now if in table1  there is no row having the value “a” in column1. So this statement will insert a row in the table1.

Now if in table1 there is a row having the value “a” in column2. So this statement will update the row’s column2  value with “c” where the column1 value is “a”.

So if you want to insert a new row otherwise update that row on the conflict of the primary key or unique index.

Insert into select

If you want to insert into a table from the selection of rows using some condition from another. It will insert one or more row at a time based on the selected rows.

Syntax:

insert into table2 (column1, column2, column3)
select column1, column2, column3
from table1
where condition;

This statement will be very useful if want inserts some calculated data in the table based on the value of data from another table or you want backup a data from one table into some other.

Insert ignore

In MySQL Insert ignore statement is another kind of insert statement in which while inserting a new row if there is an old row having the same primary key or unique index as the new row then it will delete the old rows data and insert new row data.

Insert ignore statement is same as the insert statement except in place of “insert” we write “insert ignore”.

Syntax:

insert ignore into table2 (column1, column2, column3)
values (value1, value2,value3);

insert ignore into table2
values (value1, value2,value3);

insert ignore into table2 set column1 = value1, column2 = value2, column3 = value3;

insert into table2 (column1, column2, column3)
select column1, column2, column3
from table1
where condition;

This statement can also be used as an alternative to “on duplicate update”.

Use of Limit

In MySQL,  Limit is used to show the specific number of rows while using select statement in MySQL.

Syntax:

select [column1,column2,column3] from table1 where [condition] limit 10.

This statement will show only 10 rows from the top. For example, If a select statement using a certain condition giving you 1000 or more records you want only 10. Then this requirement can be fulfilled using Limit.

There is one more way to use Limit

Syntax:

select [column1,column2,column3] from table1 where [condition] limit 11,10.

Now, this statement will again show 10 records but not from the top, actually, it will show you records from 11th to 20th row from the select statement.  For example, If a select statement using a certain condition giving you 1000 or more records. Now, you want records from 11th to 20th then you need to use limit as mentioned above. This will be very useful if you want to show data of 1000 records in the chunk of 10.

read some more concepts on MySQL on link