Thirumalai .A.P Thirumalai .A.P - 5 months ago 9
MySQL Question

MqSql - fetch rows which employee has maximum complaint severity level

I am dealing with a table 'Employee Complaint' which has columns 'EmployeeId' 'ComplaintSeverity' and 'ComplaintByUser'. ComplaintSeverity has four level 0,1,2,3.

So the table will look like this ,Example

ComplaintId|EmployeeId|ComplaintSeverity|usr_id
-----------------------------------
1 | 1 | 0 | 3
2 | 2 | 1 | 4
3 | 3 | 0 | 5
4 | 1 | 2 | 4
5 | 4 | 1 | 5
6 | 2 | 2 | 2
7 | 2 | 2 | 4


Any user can complaint employee with any of these level

When client search with severitylevel as 0,
The row should fetch as

ComplaintId|EmployeeId|ComplaintSeverity
----------------------------
3 | 3 | 0


for severitylevel as 1,

ComplaintId|EmployeeId|ComplaintSeverity
----------------------------
5 | 4 | 1


for severitylevel as 2,

ComplaintId|EmployeeId|ComplaintSeverity
----------------------------
4 | 1 | 2
6 | 2 | 2


EmployeeId 1 has been complined by 2 user with severitylevel 0,2 but his highest severity level is 2. so while searching for 0 level, 1 should not be displayed

Can anyone help me?

A J A J
Answer

You can try following query.

SELECT *
FROM
(
    SELECT cs.`EmployeeId`, MAX(cs.`ComplaintSeverity`) severity FROM ComplaintSeverity cs GROUP BY cs.`EmployeeId`
) csdata
WHERE csdata.severity=1

Replace 1 with the severity level you want.