second highest salary

Posted: June 18, 2012 in MySQL

we can get 2nd highest salary by inner query ,  not in , order by column desc limit 0,1

Create table named employee:

CREATE TABLE `employee` (

`eno` int(5) NOT NULL AUTO_INCREMENT,

`ename` varchar(50) DEFAULT NULL,

`sal` float DEFAULT NULL,

PRIMARY KEY (`eno`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

Insert the following records:

1              gopal       16000

2              sanjana   14000

3              prem      10000

Queries:

1) select sal from employee where sal = (select max(sal) from employee where sal < (select max(sal) from employee))

Ans: 14000

2) select max(sal) from employee where sal not in (select max(sal) from employee)

Ans: 14000

3)select sal from employee order by sal desc limit 1,1 (1 means 2nd highest means position ,1 no of records)

Ans: 14000

Highest salary in table using above query:

1)select sal from employee order by sal desc limit 0,1

Ans: 16000

2)select sal from employee order by sal desc limit 2,1

Ans: 10000

3) select sal from employee order by sal desc limit 0,2

Ans: 16000

14000

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s