CoolInterview.com - World's Largest Collection of Interview Questions & Answers, FAQs, queries, sample papers, exam papers, dumps, what, why, how, where, when questions
Our Services
Get 9,000 Interview Questions & Answers in an eBook.




Get it now !!
Send your Resume to 6000 Companies

Search Interview Questions

Question: How we get second highest salary in
database. Please show me this coding.

Answer: select top 1 from (select top 2 from (select salary from employee order by salary) order by salary dsc)



Category SQL Interview Questions & Answers - Exam Mode / Learning Mode
Rating (0.7) By 656 users
Added on 9/7/2013
Views 5126
Rate it!
Question: How we get second highest salary in
database. Please show me this coding.

Answer:

select top 1 from (select top 2 from (select salary from employee order by salary) order by salary dsc) Source: CoolInterview.com

Answered by: tuan | Date: 9/8/2008 | Contact tuan Contact tuan

select max(salary) from employee where salary!=(select max(salary) from employee); Source: CoolInterview.com

Answered by: senthil kumar K | Date: 9/9/2008 | Contact senthil kumar K Contact senthil kumar K

select top 1 salary from (select top 2 salary Order by salary DESC) Source: CoolInterview.com

Answered by: Hammad | Date: 9/9/2008 | Contact Hammad Contact Hammad

select max(salary) from employee where salary in (select distinct Top 2 salary from employee order by salary desc) Source: CoolInterview.com

Answered by: Sreekanth | Date: 9/14/2008 | Contact Sreekanth Contact Sreekanth

select min(sal) from (select * from table orderby desc sal) where rownum <=2 Source: CoolInterview.com

Answered by: ksailesh | Date: 9/16/2008 | Contact ksailesh Contact ksailesh

select e.salary from
(select salary from employees order by salary desc)e
where
rownum =2; Source: CoolInterview.com

Answered by: kiran jot singh | Date: 10/7/2008 | Contact kiran jot singh Contact kiran jot singh

Select salary from emplyees in (select distinct top 2 salary from employees order by

salary desc) where rownum = 2
Source: CoolInterview.com

Answered by: Karthik Gopal | Date: 11/16/2008 | Contact Karthik Gopal Contact Karthik Gopal

Select r, salary, last_name from (select rownum r, salary, last_name from (select salary, last_name from employees order by salary desc)) where r=2

u can replace value of 'r' for 'n'th person having highest salary. Source: CoolInterview.com

Answered by: Atul | Date: 11/16/2008 | Contact Atul Contact Atul

Select top 1 salary from employee where salary in (select top 2 salary from employee order by salary desc) order by salary asc Source: CoolInterview.com

Answered by: sanny | Date: 11/22/2008 | Contact sanny Contact sanny

To retrieve the second max salary from database run the following query:-

Taking EMPLOYEE as a database table and SALARY as a field where we have to find second max salary.

SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE)
Source: CoolInterview.com

Answered by: Abhimanyu Kumar Vatsa | Date: 12/1/2008 | Contact Abhimanyu Kumar Vatsa Contact Abhimanyu Kumar Vatsa

Select max(salary)from emp_salary where salary < (select max(salary) from emp-salary) Source: CoolInterview.com

Answered by: srinu | Date: 12/11/2008 | Contact srinu Contact srinu

Select max(salary)from emp_salary where salary < (select max(salary) from emp-salary) Source: CoolInterview.com

Answered by: srinu | Date: 12/11/2008 | Contact srinu Contact srinu

select salary from emp e where &n=(select count(distinct(salary)) from emp where salary>e.salary); Source: CoolInterview.com

Answered by: Raghavendraprasad | Date: 12/17/2008 | Contact Raghavendraprasad Contact Raghavendraprasad

select * from emp where sal=(select max(sal) from emp where sal not in(select max(sal) from emp)) Source: CoolInterview.com

Answered by: praneeth | Date: 12/17/2008 | Contact praneeth Contact praneeth

select MIN(salary) from table where salary in (select top 2 salary from table order by salary DESC) Source: CoolInterview.com

Answered by: AnsilMACE Puthucurichy | Date: 12/22/2008 | Contact AnsilMACE Puthucurichy Contact AnsilMACE Puthucurichy

select e1.esalary from employee e1 where 1=(select count(distinct e2.esalary) from employee e2 where e2.esalary>e1.esalary) Source: CoolInterview.com

Answered by: gopal | Date: 6/12/2009 | Contact gopal Contact gopal

Select a.sal from table a where 2= (select count(distinct b.sal) from table b where a.sal <= b.sal) Source: CoolInterview.com

Answered by: Sujay Mallesh | Date: 9/30/2009 | Contact Sujay Mallesh Contact Sujay Mallesh

Select top 1 Salary from salary_tbl where salary
IN (Select top 2 Salary from salary_tbl order by salary desc)
order by sala asc Source: CoolInterview.com

Answered by: Chandrakant | Date: 11/3/2009 | Contact Chandrakant Contact Chandrakant

The following query retrieves the nth maximum salary from emp table.

select e.sal from emp e where &n=(select count(distinct(b.sal)) from emp b where e.sal<=b.sal)

If you want to retrieve the nth minimum salary from emp table, you can give the following query

select e.sal from emp e where &n=(select count(distinct(b.sal)) from emp b where e.sal>=b.sal) Source: CoolInterview.com

Answered by: siva reddy | Date: 11/4/2009 | Contact siva reddy Contact siva reddy

select top 1 salary from
(select top 2 salary from employeemaster order by salary desc) as temp1
order by salary Source: CoolInterview.com

Answered by: govindharaju | Date: 11/4/2009 | Contact govindharaju Contact govindharaju

To find only the second highest salary:

SELECT TOP 1 salary FROM
(SELECT DISTINCT TOP 2 salary FROM employee
ORDER BY salary desc) employee
ORDER BY salary

To find the record of the employee getting second highest salary:

SELECT TOP 1 * FROM employee where salary in
(SELECT DISTINCT TOP 2 salary FROM employee ORDER BY salary desc)
ORDER BY salary Source: CoolInterview.com

Answered by: Vaithy | Date: 11/11/2009 | Contact Vaithy Contact Vaithy

select min(sal) from emp where
sal in(select distinct sal from emp
order by sal desc)
and rownum <=2 Source: CoolInterview.com

Answered by: Dipali | Date: 11/14/2009 | Contact Dipali Contact Dipali



Select b.Salary FROM (Select DISTINCT(Salary) FROM Table1) AS A,
(Select DISTINCT(Salary) FROM Table1) AS B
WHERE A.Salary >= B.Salary Group By b.Salary HAVING Count(B.Salary) =2

Value 2 will return Second Highest. Pass 3,4 for getting 3rd,4th heighest value respectively. Source: CoolInterview.com

Answered by: Ajit Pai | Date: 11/23/2009 | Contact Ajit Pai Contact Ajit Pai


SELECT salary FROM `table` ORDER BY `salary` DESC LIMIT 1 , 1;

It will return 2nd highest salary Source: CoolInterview.com

Answered by: Abhishek Karmakar | Date: 12/10/2009 | Contact Abhishek Karmakar Contact Abhishek Karmakar

select sal from
(select rownum r,sal from (select distinct sal from emp order by sal desc))
where r = 2; Source: CoolInterview.com

Answered by: Jaydeep | Date: 2/13/2010 | Contact Jaydeep Contact Jaydeep

SQL> select max(sal) from emp where sal<(select max(sal) from emp);

MAX(SAL)
----------
3000 Source: CoolInterview.com

Answered by: prasant | Date: 3/3/2010 | Contact prasant Contact prasant

select sal
from
(
select e.sal as sal,
rank() over (order by e.sal desc) as salrank
from emp e
)
where salrank = 2
Source: CoolInterview.com

Answered by: Svix | Date: 3/9/2010 | Contact Svix Contact Svix

select* from emp where sal=(select max(sal) from emp where sal < (select max(sal) from emp)); Source: CoolInterview.com

Answered by: satya | Date: 3/16/2010 | Contact satya Contact satya

Select salary
from employee a
Where 2 =
(select count(salary) From employee b Where b.salary >= a.salary);
Source: CoolInterview.com

Answered by: Kiran Vangeti | Date: 3/19/2010 | Contact Kiran Vangeti Contact Kiran Vangeti

select max(salary)
from employees
where salary < (select max(salary)
from employees); Source: CoolInterview.com

Answered by: ravi | Date: 3/19/2010 | Contact ravi Contact ravi

select max(salary) from employee where salary<(select max(salary) from employee); Source: CoolInterview.com

Answered by: shalini | Date: 3/26/2010 | Contact shalini Contact shalini

select sal from (select sal , dense_rank() over (order by sal desc) as rnk from emp)
where rnk=2 Source: CoolInterview.com

Answered by: max | Date: 4/3/2010 | Contact max Contact max

Select * from emp where sal in(select s from(select rownum r,s from(select distinct sal s from emp where order by sal desc))where r=&no);

The above query gives the o/p of 2nd,3rd,4th etc., whatever u want. Source: CoolInterview.com

Answered by: Jaalioracle | Date: 4/15/2010 | Contact Jaalioracle Contact Jaalioracle

select top 1 empsal from emp where empsal in (select distinct Top 2 empsal from emp order by empsal desc)order by empsal Source: CoolInterview.com

Answered by: amit | Date: 4/15/2010 | Contact amit Contact amit

select max(salary) from table
where salary in(select salary from table where salary betbeen max(salary)and min(salary)) Source: CoolInterview.com

Answered by: umesh | Date: 4/24/2010 | Contact umesh Contact umesh

example; table name emp

sal
9000
3000
4000
6000
find second highest salary?

select min(sal) from(select sal from emp where sal is not null order by desc)where rownum<3

mean order by desc
sal
9000
6000
4000
3000
where rownum<3
mean
sal
9000
6000
& selact min(sal) that is 6000(it is also second highaest salary.

Thank you Source: CoolInterview.com

Answered by: Himanshu sharma | Date: 5/1/2010 | Contact Himanshu sharma Contact Himanshu sharma

SELECT TOP 1 salary FROM ( SELECT TOP 2 salary FROM employee_table ORDER BY salary DESC)ORDER BY salary; Source: CoolInterview.com

Answered by: dasteswarudu.d | Date: 5/2/2010 | Contact dasteswarudu.d Contact dasteswarudu.d

SELECT TOP (1) SAL
FROM (SELECT TOP (2) SAL
FROM SALARY
ORDER BY SAL DESC) AS a
ORDER BY SAL Source: CoolInterview.com

Answered by: Aslam | Date: 5/14/2010 | Contact Aslam Contact Aslam

select max(sal) from emp where sal
not in(select max(sal) from emp); Source: CoolInterview.com

Answered by: madhavi | Date: 5/25/2010 | Contact madhavi Contact madhavi

This is the best query to find out nth highest salary

SELECT *
FROM
(
SELECT E.ENAME,E.SAL,E.DEPTNO, DENSE_RANK () OVER ( ORDER BY SAL DESC) RANK
FROM EMP E
)
WHERE RANK=2
Source: CoolInterview.com

Answered by: Sateswar | Date: 6/30/2010 | Contact Sateswar Contact Sateswar

select distinct(a.salary) from emp a where 2 = (select count(distinct(b.salary)) from emp b where a.salary <= b.salary) Source: CoolInterview.com

Answered by: latha | Date: 7/10/2010 | Contact latha Contact latha

select min(salary) from tab where salary in (select distinct Top 2 salary from table order by salary desc) Source: CoolInterview.com

Answered by: Puneet | Date: 7/26/2010 | Contact Puneet Contact Puneet

forget the logic

Empsal table data
700
500
100
900
400
200
600
750


query to find second highest salary from table Empsal

mysql>select distinct(max(e.salary)) from Empsal e
>where e.salary in(
>select e1.salary from Empsal e1 where e1.salary<(select max(salary) from Empsal));

Output=750

Source: CoolInterview.com

Answered by: amit singh | Date: 8/9/2010 | Contact amit singh Contact amit singh

Empsal table data
700
500
100
900
400
200
600
750
query to find the third highest salary

mysql>select distinct(max(e.salary)) from Empsal e
>where e.salary in(
>select e1.salary from Empsal e1 where e1.salary<
>(select max(e1.salary) from Empsal e1
>where e1.salary IN(
>select e2.salary from Empsal e2 where
>e2.salary<(select max(salary) from Empsal))));
Output=700
Source: CoolInterview.com

Answered by: amit singh | Date: 8/9/2010 | Contact amit singh Contact amit singh

Empsal table data
700
500
100
900
400
200
600
750
query to find third highest
mysql>select distinct(max(e.salary)) from Empsal e
>where e.salary in(
>select e1.salary from Empsal e1 where e1.salary<
>(select max(e1.salary) from Empsal e1
>where e1.salary IN(
>select e2.salary from Empsal e2 where
>e2.salary<(select max(salary) from Empsal))));
Output=700

Source: CoolInterview.com

Answered by: amit singh | Date: 8/9/2010 | Contact amit singh Contact amit singh

for oracle query is:

select min(d.sal) from (select e.sal from (select sal from emp order by sal desc) e where rownum < 3) d Source: CoolInterview.com

Answered by: Yogendra Kirar | Date: 8/21/2010 | Contact Yogendra Kirar Contact Yogendra Kirar

select max(sal) from EMP A
where 2=(select count (sal) from EMP B where B.sal >= A.sal) Source: CoolInterview.com

Answered by: Giri Naidu | Date: 9/6/2010 | Contact Giri Naidu Contact Giri Naidu

Select distinct sal from emp where rownum = 2 Source: CoolInterview.com

Answered by: karthikeya | Date: 9/9/2010 | Contact karthikeya Contact karthikeya


If you have the better answer, then send it to us. We will display your answer after the approval

Rules to Post Answers in CoolInterview.com:-

  • There should not be any Spelling Mistakes.
  • There should not be any Gramatical Errors.
  • Answers must not contain any bad words.
  • Answers should not be the repeat of same answer, already approved.
  • Answer should be complete in itself.

Post your answer here

Inform me about updated answers to this question.
Related Questions
View Answer
How to analyze the performance of a query using Explain Plan? Can any one explain me this in detail... if you can provide me with a link where I can get full information on it I would appreciate it...?
View Answer
Explain the concept of joins and list out the different type of joins?
View Answer
What is the difference between varchar & varchar2?
View Answer
what is analyze command how to use ?
View Answer
How you define which session run and how long time ?
View Answer
How to save the output of a query/ stored procedure to a text file using T-SQL?
View Answer
How to get the first day of the week, last day of the week and last day of the month using T-SQL date functions?
View Answer
How to programmatically find out when the SQL Server service started?
View Answer
Write a Query to find unique names of authors who have written books. (Using Pubs database)?
View Answer
Write a Query to pull out the maximum unit price for each in the database. (Using Northwind database)
View Answer
Write a SQL statement to draw the second highest salary in Employee Table (Using Sub query)
Table structure is:
Employe ID
Name
Salary
View Answer
What is a default TCP/IP socket assigned for SQL Server?
View Answer
What is a default TCP/IP socket assigned for SQL Server?
View Answer
What?s the difference between a primary key and a unique key?
View Answer
How to retrieve the image from the database?
View Answer
How to find the second highest salary from employee table? and how to write it?
View Answer
How to transfer some amount from one person's account to another's ?
View Answer
Advantages and disadvantages of attributes.
View Answer
What is sql? what is the difference between sql and pl/sql?
View Answer
Why do we use cache and no cache in sequences? What is the benefit we have in using them?
View Answer

Please Note: We keep on updating better answers to this site. In case you are looking for Jobs, Pls Click Here Vyoms.com - Best Freshers & Experienced Jobs Website.

View All SQL Interview Questions & Answers - Exam Mode / Learning Mode




India News Network
Latest 20 Questions
Payment of time- barred debt is: (a) Valid (b) Void (c) Illegal (d) Voidable
Consideration is defined in the Indian Contract Act,1872 in: (a) Section 2(f) (b) Section 2(e) (c) Section 2(g) (d) Section 2(d)
Which of the following is not an exception to the rule, "No consideration, No contract": (a) Natural love and affection (b) Compensation for involuntary services (c) Completed gift (d) Agency
Consideration must move at the desire of: (a) The promisor (b) The promisee (c) The promisor or any other party (d) Both the promisor and the promisee
An offer which is open for acceptance over a period of time is: (a) Cross Offer (b) Counter Offer (c) Standing Offer (d) Implied Offer
Specific offer can be communicated to__________ (a) All the parties of contract (b) General public in universe (c) Specific person (d) None of the above
_________ amounts to rejection of the original offer. (a) Cross offer (b) Special offer (c) Standing offer (d) Counter offer
A advertises to sell his old car by advertising in a newspaper. This offer is caleed: (a) General Offer (b) Special Offer (c) Continuing Offer (d) None of the above
In case a counter offer is made, the original offer stands: (a) Rejected (b) Accepted automatically (c) Accepted subject to certain modifications and variations (d) None of the above
In case of unenforceable contract having some technical defect, parties (a) Can sue upon it (b) Cannot sue upon it (c) Should consider it to be illegal (d) None of the above
If entire specified goods is perished before entering into contract of sale, the contract is (a) Valid (b) Void (c) Voidable (d) Cancelled
______________ contracts are also caled contracts with executed consideration. (a) Unilateral (b) Completed (c) Bilateral (d) Executory
A offers B to supply books @ Rs 100 each but B accepts the same with condition of 10% discount. This is a case of (a) Counter Offer (b) Cross Offer (c) Specific Offer (d) General Offer
_____________ is a game of chance. (a) Conditional Contract (b) Contingent Contract (c) Wagering Contract (d) Quasi Contract
There is no binding contract in case of _______ as one's offer cannot be constructed as acceptance (a) Cross Offer (b) Standing Offer (c) Counter Offer (d) Special Offer
An offer is made with an intention to have negotiation from other party. This type of offer is: (a) Invitation to offer (b) Valid offer (c) Voidable (d) None of the above
When an offer is made to the world at large, it is ____________ offer. (a) Counter (b) Special (c) General (d) None of the above
Implied contract even if not in writing or express words is perfectly _______________ if all the conditions are satisfied:- (a) Void (b) Voidable (c) Valid (d) Illegal
A specific offer can be accepted by ___________. (a) Any person (b) Any friend to offeror (c) The person to whom it is made (d) Any friend of offeree
An agreement toput a fire on a person's car is a ______: (a) Legal (b) Voidable (c) Valid (d) Illegal
Cache = 0.03125 Seconds