How to find out department wise second maximum salary.

Code
SELECT deptno,
       sal,
       dr
FROM
  (SELECT deptno,
          sal,
          dense_rank()over(partition BY deptno
                           ORDER BY sal DESC) dr
   FROM emp)
WHERE dr = 2
 
 

Code
SELECT max(salary)
FROM dept
WHERE max(salary)<
    (SELECT MAX (salary)
     FROM dept);
 

Code
SELECT max(sal)
FROM emp
WHERE sal NOT IN
    (SELECT max(sal)
     FROM emp
     GROUP BY deptno)
GROUP BY deptno;
 

Code
SELECT DISTINCT d1.deptno,
                e1.sal
FROM emp e1,
     dept d1
WHERE 2 =
    (SELECT count(DISTINCT e2.sal)
     FROM emp e2,
          dept d2
     WHERE e2.sal >= e1.sal
       AND d2.deptno = e2.deptno
       AND d1.deptno = d2.deptno)
  AND d1.deptno = e1.deptno

Code
SELECT max(e1.sal),
       e1.deptno
FROM emp e1
WHERE sal <
    (SELECT max(sal)
     FROM emp e2
     WHERE e2.deptno = e1.deptno)
GROUP BY e1.deptno
 

Code
SELECT max(sal)
FROM
WHERE sal NOT IN
    (SELECT max(sal)
     FROM
     GROUP BY deptno)
GROUP BY deptno;
 

Try this it is working

Code
SELECT *
FROM
  (SELECT ename ,
          sal,
          deptno ,
          rank() over (PARTITION BY deptno
                       ORDER BY sal DESC) max_sal
   FROM emp)
WHERE max_sal= 2;
 
 

Code
SELECT max(sal)
FROM empwhere sal NOT IN
  (SELECT max(sal)
   FROM emp
   GROUP BY deptno)
GROUP BY deptno
 
 

Code
SELECT salary,
       deptid
FROM emp e1
WHERE 2=
    (SELECT count(DISTINCT(salary))
     FROM emp e2
     WHERE e1.salary<=e2.salary
     GROUP BY deptid)
 
 

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

Code
SELECT *
FROM
  (SELECT t.*,
          rownum rnum
   FROM
     (SELECT sal
      FROM employee
      ORDER BY comp_dept DESC) t
   WHERE rownum<2)
WHERE rnum>2
 
 

Deepika Jain

Code
SELECT*
FROM Emptbl e
INNER JOIN
  (SELECT Dep,
          MAX(salary) AS sal
   FROM Emptbl
   GROUP BY Dep) AS t ON t.Dep = e.Dep
WHERE e.Salary < t.sal
 
 

Table Name: employee
columns: employee,salary, dept

Code
SELECT MAX (e1.salary) , e1.dept
FROM employee e1
WHERE e1.salary<
    (SELECT MAX (e2.salary)
     FROM employee e2
     WHERE e1.dept= e2.dept
     GROUP BY e2.dept)
GROUP BY e1.dep
 
 

Code
SELECT *
FROM emp e,
  (SELECT dept,
          MAX(salary) AS salary
   FROM emp
   GROUP BY dept) e1
WHERE e1.dept=e.dept
  AND e.salary=e1.salary

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

Code
SELECT max(sal) ,
       deptno
FROM emp e1 ,
  (SELECT max(sal) e2sal ,
          deptno e2dept
   FROM emp
   GROUP BY deptno) e2
WHERE e2.e2dept = e1.dept
  AND e1.sal < e2.e2sal
GROUP BY e1.deptno
 
 

Code
SELECT LEVEL,max(salary)
FROM employee
WHERE LEVEL=&LEVEL NO CONNECT BY
  PRIOR salary>salary
GROUP BY LEVEL;
 
 

Code
SELECT max(salary)
FROM emp
GROUP BY dept HAVING salary <
  (SELECT max(salary)
   FROM emp
   GROUP BY dept)
 
 

Code
SELECT deptno,
       max(salary)
FROM emp
WHERE salary NOT IN
    (SELECT max(salary)
     FROM emp
     GROUP BY deptno)
GROUP BY deptno;
 
 

Code
SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL
FROM EMP E1
WHERE 1 =
    (SELECT COUNT(DISTINCT(SAL))
     FROM EMP E2
     WHERE E1.SAL <= E2.SAL)
 
 

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

What is the length of column in oracle?

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

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

Code
SELECT min(salary) 
FROM dept
WHERE salary IN ( SELECT DISTINCT top 2 salary
                        FROM dept
                        ORDER BY salary DESC)

Code
SELECT DISTINCT d1.depno,
                e1.salary,e1.empid,d1.location
FROM EMPLOYEE1 e1,
     dept d1
WHERE 2 =
    (SELECT count(DISTINCT e2.salary)
     FROM EMPLOYEE1 e2,
          dept d2
     WHERE e2.salary >= e1.salary
       AND d2.depno = e2.Dep_No
       AND d1.depno = d2.depno)
  AND d1.depno = e1.Dep_No