I am writing one query to get results based on parent table records and latest child table id based on condition
Sample Data:
*Department*
Id Name
-----------
1 Dept1
2 Dept2
3 Dept3
4 Dept4
*Employee*
Id DeptId quality VerifiedOn
----------------------------------
1 1 Ok '2014-08-01'
2 2 Ok '2014-09-01'
3 2 Best '2014-08-01'
4 2 Good '2014-08-07'
5 4 Good '2014-10-08'
6 4 Ok '2014-10-01'
7 4 Good '2014-09-01'
Requirement
I need to get all department details along with latest good quality employee Id.
Good Quality Employee logic is :
- Take Latest employeeId of Quality is ‘Best’
- If not Best quality is there, take latest of employeeId
- If no employee available for the department return 0 or NULL
[top 1 order by VerifiedOn desc]
Need to create viwew for this
Expected Result:
DeptId DeptName EmpiId
-----------------------
1 Dept1 1
2 Dept2 3
3 Dept3 0
4 Dept4 5
6
This should do the trick:
WITH cteEmployee As
(
SELECT
Id,
DeptId,
quality,
VerifiedOn,
ROW_NUMBER() OVER
(
PARTITION BY
DeptId
ORDER BY
CASE quality WHEN 'Best' THEN 0 ELSE 1 END,
VerifiedOn DESC
) As RN
FROM
Employee
)
SELECT
D.Id,
D.Name,
E.Id,
E.quality,
E.VerifiedOn
FROM
Department As D
LEFT JOIN cteEmployee As E ON E.DeptId = D.Id And E.RN = 1
ORDER BY
D.Name
;
Results:
Id | Name | Id | quality | VerifiedOn |
---|---|---|---|---|
1 | Dept1 | 1 | Ok | 2014-08-01 |
2 | Dept2 | 3 | Best | 2014-08-01 |
3 | Dept3 | null | null | null |
4 | Dept4 | 5 | Good | 2014-10-08 |
ROW_NUMBER (Transact-SQL) – SQL Server | Microsoft Learn
I tested the query on a DB Fiddle on MySQL v9 and I got your expected results.
What this does: I use ROW_NUMBER() and PARTITION BY e.DeptID to rank the employees in each department.
Then I sorted them first by Quality and secondly by VerifiedOn
This allows me with rn = 1 to return only the latest “Good Quality” employee from every department
On the Final SELECT its just a LEFT JOIN between both.
To ensure departments with no employee who meets the requirements COALESCE(le.EmpID,0) replaces any NULL values with 0
WITH LatestEmployee AS (
SELECT
e.Id AS EmpId,
e.DeptId,
e.Quality,
e.VerifiedOn,
ROW_NUMBER() OVER (
PARTITION BY e.DeptId
ORDER BY
CASE WHEN e.Quality = 'Best' THEN 1 ELSE 2 END,
e.VerifiedOn DESC
) AS rn
FROM
Employee e
)
SELECT
d.Id AS DeptId,
d.Name AS DeptName,
COALESCE(le.EmpId, 0) AS EmpId
FROM
Department d
LEFT JOIN
LatestEmployee le ON d.Id = le.DeptId AND le.rn = 1
ORDER BY
d.Id;
I used 3 sub queries to search for the employees.
SELECT d.*,
COALESCE
(
(SELECT top 1 e.ID
FROM Employee e
WHERE e.deptID=d.Id AND e.quality='Best'
ORDER BY VerifiedOn desc),
(SELECT top 1 e.ID
FROM Employee e
WHERE e.deptID=d.Id AND e.quality='Good'
ORDER BY VerifiedOn desc),
(SELECT top 1 e.ID
FROM Employee e
WHERE e.deptID=d.Id AND e.quality='Ok'
ORDER BY VerifiedOn desc),
'0'
) as EmpiId
FROM Department d
GROUP BY d.ID,d.Name
Alternative suggested by Siggemannen:
SELECT d.*,
ISNULL
(
(SELECT top 1 e.ID
FROM Employee e
WHERE e.deptID=d.Id
ORDER BY CASE e.quality
WHEN 'Best' THEN 0
WHEN 'Good' THEN 1
WHEN 'OK' THEN 2
Else 3
END,
VerifiedOn desc),
0
) as EmpiId
FROM Department d
GROUP BY d.ID,d.Name
fiddle
Id | Name | EmpiId |
---|---|---|
1 | Dept1 | 1 |
2 | Dept2 | 3 |
3 | Dept3 | 0 |
4 | Dept4 | 5 |
5