GeekInterview.com
Series: Subject:
Question: 209 of 234

How to find out department wise second maximum salary.

Asked by: Interview Candidate | Asked on: Mar 29th, 2006
Showing Answers 1 - 25 of 25 Answers
Irfan

Answered On : Mar 30th, 2006

Code
  1. SELECT deptno,
  2.        sal,
  3.        dr
  4. FROM
  5.   (SELECT deptno,
  6.           sal,
  7.           dense_rank()over(partition BY deptno
  8.                            ORDER BY sal DESC) dr
  9.    FROM emp)
  10. WHERE dr = 2
  11.  
  12.  

  
Login to rate this answer.
sreelekha

Answered On : Apr 9th, 2006

View all answers by sreelekha

Code
  1. SELECT max(salary)
  2. FROM dept
  3. WHERE max(salary)<
  4.     (SELECT MAX (salary)
  5.      FROM dept);
  6.  

  
Login to rate this answer.

Code
  1. SELECT max(sal)
  2. FROM emp
  3. WHERE sal NOT IN
  4.     (SELECT max(sal)
  5.      FROM emp
  6.      GROUP BY deptno)
  7. GROUP BY deptno;
  8.  

Yes  3 Users have rated as useful.
  
Login to rate this answer.
ardsouza

Answered On : May 16th, 2006

View all answers by ardsouza

Code
  1. SELECT DISTINCT d1.deptno,
  2.                 e1.sal
  3. FROM emp e1,
  4.      dept d1
  5. WHERE 2 =
  6.     (SELECT count(DISTINCT e2.sal)
  7.      FROM emp e2,
  8.           dept d2
  9.      WHERE e2.sal >= e1.sal
  10.        AND d2.deptno = e2.deptno
  11.        AND d1.deptno = d2.deptno)
  12.   AND d1.deptno = e1.deptno

  
Login to rate this answer.
Bhai

Answered On : Jun 9th, 2006

Code
  1. SELECT max(e1.sal),
  2.        e1.deptno
  3. FROM emp e1
  4. WHERE sal <
  5.     (SELECT max(sal)
  6.      FROM emp e2
  7.      WHERE e2.deptno = e1.deptno)
  8. GROUP BY e1.deptno
  9.  

Yes  3 Users have rated as useful.
  
Login to rate this answer.

Code
  1. SELECT max(sal)
  2. FROM
  3. WHERE sal NOT IN
  4.     (SELECT max(sal)
  5.      FROM
  6.      GROUP BY deptno)
  7. GROUP BY deptno;
  8.  


Try this it is working

  
Login to rate this answer.
Imran_Javed

Answered On : May 9th, 2008

View all answers by Imran_Javed

Code
  1. SELECT *
  2. FROM
  3.   (SELECT ename ,
  4.           sal,
  5.           deptno ,
  6.           rank() over (PARTITION BY deptno
  7.                        ORDER BY sal DESC) max_sal
  8.    FROM emp)
  9. WHERE max_sal= 2;
  10.  
  11.  

  
Login to rate this answer.
monika1985

Answered On : May 28th, 2008

View all answers by monika1985

Code
  1. SELECT max(sal)
  2. FROM empwhere sal NOT IN
  3.   (SELECT max(sal)
  4.    FROM emp
  5.    GROUP BY deptno)
  6. GROUP BY deptno
  7.  
  8.  

Yes  1 User has rated as useful.
  
Login to rate this answer.
mamtachaplot

Answered On : Aug 6th, 2008

View all answers by mamtachaplot

Code
  1. SELECT salary,
  2.        deptid
  3. FROM emp e1
  4. WHERE 2=
  5.     (SELECT count(DISTINCT(salary))
  6.      FROM emp e2
  7.      WHERE e1.salary<=e2.salary
  8.      GROUP BY deptid)
  9.  
  10.  


group by deptid
Assuming table is emp(eid,deptid,salary)

  
Login to rate this answer.

Code
  1. SELECT *
  2. FROM
  3.   (SELECT t.*,
  4.           rownum rnum
  5.    FROM
  6.      (SELECT sal
  7.       FROM employee
  8.       ORDER BY comp_dept DESC) t
  9.    WHERE rownum<2)
  10. WHERE rnum>2
  11.  
  12.  


Deepika Jain

  
Login to rate this answer.
agssiva

Answered On : Mar 25th, 2010

View all answers by agssiva

Code
  1. SELECT*
  2. FROM Emptbl e
  3. INNER JOIN
  4.   (SELECT Dep,
  5.           MAX(salary) AS sal
  6.    FROM Emptbl
  7.    GROUP BY Dep) AS t ON t.Dep = e.Dep
  8. WHERE e.Salary < t.sal
  9.  
  10.  

  
Login to rate this answer.
rashmi.purbey

Answered On : Jul 7th, 2010

View all answers by rashmi.purbey

Table Name: employee
columns: employee,salary, dept

Code
  1. SELECT MAX (e1.salary) , e1.dept
  2. FROM employee e1
  3. WHERE e1.salary<
  4.     (SELECT MAX (e2.salary)
  5.      FROM employee e2
  6.      WHERE e1.dept= e2.dept
  7.      GROUP BY e2.dept)
  8. GROUP BY e1.dep
  9.  
  10.  

  
Login to rate this answer.
subratpadhi87

Answered On : Jun 6th, 2011

View all answers by subratpadhi87

Code
  1. SELECT *
  2. FROM emp e,
  3.   (SELECT dept,
  4.           MAX(salary) AS salary
  5.    FROM emp
  6.    GROUP BY dept) e1
  7. WHERE e1.dept=e.dept
  8.   AND e.salary=e1.salary

  
Login to rate this answer.
sagar

Answered On : Sep 29th, 2011

It's wrong.better to use dense_rank() function instead of rank() function

  
Login to rate this answer.
Mohan Rao

Answered On : Nov 3rd, 2011

Code
  1. SELECT max(sal) ,
  2.        deptno
  3. FROM emp e1 ,
  4.   (SELECT max(sal) e2sal ,
  5.           deptno e2dept
  6.    FROM emp
  7.    GROUP BY deptno) e2
  8. WHERE e2.e2dept = e1.dept
  9.   AND e1.sal < e2.e2sal
  10. GROUP BY e1.deptno
  11.  
  12.  

  
Login to rate this answer.
sharika

Answered On : Nov 24th, 2011

Code
  1. SELECT LEVEL,max(salary)
  2. FROM employee
  3. WHERE LEVEL=&LEVEL NO CONNECT BY
  4.   PRIOR salary>salary
  5. GROUP BY LEVEL;
  6.  
  7.  

  
Login to rate this answer.
shruthyreddy

Answered On : Jan 9th, 2012

View all answers by shruthyreddy

Code
  1. SELECT max(salary)
  2. FROM emp
  3. GROUP BY dept HAVING salary <
  4.   (SELECT max(salary)
  5.    FROM emp
  6.    GROUP BY dept)
  7.  
  8.  

  
Login to rate this answer.
srikanth

Answered On : Jan 11th, 2012

Code
  1. SELECT deptno,
  2.        max(salary)
  3. FROM emp
  4. WHERE salary NOT IN
  5.     (SELECT max(salary)
  6.      FROM emp
  7.      GROUP BY deptno)
  8. GROUP BY deptno;
  9.  
  10.  

  
Login to rate this answer.
SASI

Answered On : Apr 7th, 2012

Code
  1. SELECT EMPNO,
  2.        ENAME,
  3.        DEPTNO,
  4.        SAL
  5. FROM EMP E1
  6. WHERE 1 =
  7.     (SELECT COUNT(DISTINCT(SAL))
  8.      FROM EMP E2
  9.      WHERE E1.SAL <= E2.SAL)
  10.  
  11.  

  
Login to rate this answer.
vipinganganiya

Answered On : Apr 26th, 2012

View all answers by vipinganganiya

select max(salary) from dept order by dept_id desc limit 1,1

  
Login to rate this answer.
Ashok

Answered On : May 4th, 2012

What is the length of column in oracle?

  
Login to rate this answer.
smily08

Answered On : Aug 30th, 2012

View all answers by smily08

select max(salary) from EMP
WHERE SALARY < (SELECT MAX(SALARY) FROM EMP where dept = Dept1) and dept = Dept1

  
Login to rate this answer.
smily08

Answered On : Aug 30th, 2012

View all answers by smily08

select max(salary) from dept order by dept_id desc limit 1,1

  
Login to rate this answer.
Nazeera Jaffar

Answered On : Sep 25th, 2012

Code
  1. SELECT min(salary)
  2. FROM dept
  3. WHERE salary IN ( SELECT DISTINCT top 2 salary
  4.                         FROM dept
  5.                         ORDER BY salary DESC)

  
Login to rate this answer.
Mrinal

Answered On : Dec 14th, 2012

Code
  1. SELECT DISTINCT d1.depno,
  2.                 e1.salary,e1.empid,d1.location
  3. FROM EMPLOYEE1 e1,
  4.      dept d1
  5. WHERE 2 =
  6.     (SELECT count(DISTINCT e2.salary)
  7.      FROM EMPLOYEE1 e2,
  8.           dept d2
  9.      WHERE e2.salary >= e1.salary
  10.        AND d2.depno = e2.Dep_No
  11.        AND d1.depno = d2.depno)
  12.   AND d1.depno = e1.Dep_No

  
Login to rate this answer.

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

Related Open Questions

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Interview Question

 Ask Interview Question?

 

Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.