First Two Salaries in Each Department

Query top 2 salaries in each department

You can't reference a calculated column directly in the where clause. Use a CTE or sub-query.

with cte as (
    select a.Name, a.salary a.[Dept id], b.Dept_Name
        , row_number() over (partition by b.[Dept Name] order by a.salary) as row_rank
    from dbo.d as a
    inner join dbo.e as b on a.[Dept id] = b.[Dept id]
)
select *
from cte
where row_rank < 3;

You don't need [dept id] from both tables as they are the same. Also its advised to alias your tables with a meaningful alias, not just a, b etc

mysql query To get the top two salary from each department

SELECT emp1.departid , emp1.salary
FROM Department emp1
WHERE (
SELECT COUNT(DISTINCT(emp2.salary))
FROM Department emp2
WHERE emp2.salary > emp1.salary and emp1.departid  = emp2.departid ) in (0,1) group by emp1.departid , emp1.salary

I have tested this solution in sql server. But i think it will work in ms sql as well.

I want to query difference between first and second highest salary

Use the DENSE_RANK (if you want highest two unique salary values) or ROW_NUMBER (if you want the salary of the two highest earning people regardless of ties) analytic functions and then use conditional aggregation:

SELECT MAX( CASE WHEN sal_rank = 1 THEN sal END )
         - MAX( CASE WHEN sal_rank = 2 THEN sal END ) AS sal_difference_ignore_ties,
       MAX( CASE WHEN sal_rownum = 1 THEN sal END )
         - MAX( CASE WHEN sal_rownum = 2 THEN sal END ) AS sal_difference_with_ties
FROM   (
  SELECT e.deptno,
         e.sal,
         DENSE_RANK() OVER ( ORDER BY e.sal DESC ) AS sal_rank,
         ROW_NUMBER() OVER ( ORDER BY e.sal DESC ) AS sal_rownum
  FROM   emp e
         INNER JOIN dept d
         ON ( e.deptno = d.deptno )
  WHERE  d.loc = 'New York'
);

Which, for the sample data:

CREATE TABLE emp ( deptno, sal ) AS
SELECT 1, 2000 FROM DUAL UNION ALL
SELECT 1, 2000 FROM DUAL UNION ALL
SELECT 1, 1000 FROM DUAL UNION ALL
SELECT 1,  500 FROM DUAL UNION ALL
SELECT 1,  200 FROM DUAL UNION ALL
SELECT 2, 2000 FROM DUAL UNION ALL
SELECT 2, 1500 FROM DUAL UNION ALL
SELECT 2,  200 FROM DUAL UNION ALL
SELECT 2,  700 FROM DUAL;

CREATE TABLE dept ( deptno, loc ) AS
SELECT 1, 'New York' FROM DUAL UNION ALL
SELECT 2, 'Beijing'  FROM DUAL;

Outputs:

SAL_DIFFERENCE_IGNORE_TIES | SAL_DIFFERENCE_WITH_TIES
-------------------------: | -----------------------:
                      1000 |                        0

---

Or, if you want all the departments:

SELECT deptno,
       MAX( CASE WHEN sal_rank = 1 THEN sal END )
         - MAX( CASE WHEN sal_rank = 2 THEN sal END ) AS sal_difference_ignore_ties,
       MAX( CASE WHEN sal_rownum = 1 THEN sal END )
         - MAX( CASE WHEN sal_rownum = 2 THEN sal END ) AS sal_difference_with_ties
FROM   (
  SELECT e.deptno,
         e.sal,
         DENSE_RANK() OVER ( PARTITION BY e.deptno ORDER BY e.sal DESC ) AS sal_rank,
         ROW_NUMBER() OVER ( PARTITION BY e.deptno ORDER BY e.sal DESC ) AS sal_rownum
  FROM   emp e
         INNER JOIN dept d
         ON ( e.deptno = d.deptno )
)
GROUP BY deptno;SELECT deptno,
       MAX( CASE WHEN sal_rank = 1 THEN sal END )
         - MAX( CASE WHEN sal_rank = 2 THEN sal END ) AS sal_difference_ignore_ties,
       MAX( CASE WHEN sal_rownum = 1 THEN sal END )
         - MAX( CASE WHEN sal_rownum = 2 THEN sal END ) AS sal_difference_with_ties
FROM   (
  SELECT e.deptno,
         e.sal,
         DENSE_RANK() OVER ( PARTITION BY e.deptno ORDER BY e.sal DESC ) AS sal_rank,
         ROW_NUMBER() OVER ( PARTITION BY e.deptno ORDER BY e.sal DESC ) AS sal_rownum
  FROM   emp e
         INNER JOIN dept d
         ON ( e.deptno = d.deptno )
)
GROUP BY deptno;

Which outputs:

DEPTNO | SAL_DIFFERENCE_IGNORE_TIES | SAL_DIFFERENCE_WITH_TIES
-----: | -------------------------: | -----------------------:
     1 |                       1000 |                        0
     2 |                        500 |                      500

db<>fiddle here

How to get max salary in each dept from 2 tables - sql?

Join the tables and the query that returns the max salary for each department:

select
  d.name_dep, e.person_name, t.salary
from Dep d inner join (
  select id_dep, max(salary) salary
  from Emp
  group by id_dep
) t on t.id_dep = d.id_dep
inner join Emp e on e.id_dep = t.id_dep and e.salary = t.salary

See the demo.

Results:

| name_dep    | person_name | salary |
| ----------- | ----------- | ------ |
| energy      | borat       | 300    |
| econ        | erjan       | 1600   |
| credit      | sergey      | 1000   |
| manufactury | lucy        | 20     |
| retail      | mike        | 1200   |

Get difference in top 2 salary of employee from each department

You can give the following query a try:

SELECT Dept, MAX(Rank) AS Rank,
       SUM(CASE 
              WHEN rnk = 1 THEN Sal 
              WHEN rnk = 2 THEN -Sal 
              ELSE 0
           END) AS diff
FROM (
   SELECT @rnk := IF(@dept = Dept, @rnk + 1,
                     IF(@dept := Dept, 1, 1)) AS rnk,
          Dept, Sal, Rank                  
   FROM emp
   CROSS JOIN (SELECT @rnk := 0, @dept = '') AS vars
   ORDER BY Dept, Rank DESC, Sal DESC) AS t
GROUP BY Dept   

The query uses variables to assign a rank number to each record depending on Rank and Sal values. The outer query consumes the variable values and performs conditional aggregation to calculate the difference between the first and the second ranking records.

Demo here

---

Related Topics