Професионална програма
Loading...
tiapko avatar tiapko 31 Точки

Problem 18. *3rd Highest Salary - judge

Здравейте,

 

Решавам задача "Problem 18.   *3rd Highest Salary" / Exercises: Data Aggregation, и изкарвам отговора с това querry, но judge не го приема за верен:

 

CREATE TABLE tempDB (DepartmentID INT, ThirdHighestSalary DECIMAL(10,2));
DECLARE @cnt INT = (SELECT DISTINCT min(departmentId) FROM Employees)
DECLARE @cnt_total int = (SELECT DISTINCT max(departmentId) FROM Employees)
WHILE @cnt <= @cnt_total
BEGIN
INSERT INTO tempDB (DepartmentID,ThirdHighestSalary)
SELECT DISTINCT departmentId, Salary AS [ThirdHighestSalary] FROM Employees 
 WHERE DepartmentID = @cnt ORDER BY Salary DESC OFFSET 2 ROW FETCH NEXT 1 ROW only
   SET @cnt = @cnt + 1;
END;
SELECT * FROM tempDB;

 

Това querry изкарва същият отговор и judge го приема:

 

SELECT DepartmentID, 
(SELECT DISTINCT Salary FROM Employees 
WHERE DepartmentID = e.DepartmentID 
ORDER BY Salary DESC OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY) AS ThirdHighestSalary
FROM Employees e
WHERE (SELECT DISTINCT Salary FROM Employees 
WHERE DepartmentID = e.DepartmentID 
ORDER BY Salary DESC OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY) IS NOT NULL
GROUP BY DepartmentID

 

Можете ли да ми кажете на какво се дължи това ?

 

Тагове:
0
Module: C# DB 01/02/2017 11:17:25
DDaskalov avatar DDaskalov 10 Точки

Поздравления за елегантното решение... моето се получи поне 3-4 четири пъти по-дълго и още се опитвам да разбера какво точно си направил... ще се чете...

0
lapd87 avatar lapd87 103 Точки
SELECT 
    `department_id`,
    (SELECT DISTINCT
            `e2`.`salary`
        FROM
            `employees` AS `e2`
        WHERE
            `e2`.`department_id` = `e1`.`department_id`
        ORDER BY `e2`.`salary` DESC
        LIMIT 2 , 1) AS `third_highest_salary`
FROM
    `employees` AS `e1`
GROUP BY `department_id`
HAVING `third_highest_salary` IS NOT NULL;

малко подобрение за да избегнем повтарянето

1
pirocorp avatar pirocorp 19 Точки

Аз лично е реших така:

 

  WITH SalaryRank 
       AS(SELECT DepartmentID,
                 Salary,
                 DENSE_RANK() OVER(PARTITION BY DepartmentID ORDER BY Salary DESC) AS [Rank]
            FROM Employees)
SELECT DISTINCT DepartmentID,
       Salary AS ThirdHighestSalary
  FROM SalaryRank
 WHERE [Rank] = 3

 

1
25/11/2018 18:10:03
Cemish avatar Cemish 18 Точки

И аз реших така:

SELECT DISTINCT  DepartmentID,Salary AS 'ThirdHighestSalary'
FROM(
SELECT Employees.DepartmentID,Employees.Salary,DENSE_RANK() OVER   
    (PARTITION BY Employees.DepartmentID ORDER BY Employees.Salary DESC) AS Rank
FROM Employees

) AS Tbl01

WHERE Tbl01.Rank=3

 

ИЛИ

 

SELECT  DepartmentID,Salary AS [ThirdHighestSalary]
FROM(
SELECT Employees.DepartmentID,Employees.Salary,DENSE_RANK() OVER   
    (PARTITION BY Employees.DepartmentID ORDER BY Employees.Salary DESC) AS Rank
FROM Employees

) AS Tbl01

WHERE Tbl01.Rank=3
GROUP BY DepartmentID,Salary

0
21/06/2021 11:18:55