CODEWITHSUNDEEP
mysqlsql
osumatu
osumatu

Reputation: 442

Unique join by condition on MySQL

I have a table containing employees and in this table I have following data:

id          username   employee_status
33189       SLEE       3
36351       SLEE       0
29096595    SLEE       0
19197668    AARM       0
20738021    AARM       0
29097305    AARM       0

While employee status 3 means active, 0 means non-active. So this data means user SLEE has been transferred couple of times and still working in the company and user AARM has been transferred couple of times and left the company.

What I need is to distinct join on this table. I could write a query like that:

SELECT username
    , MAX(id)
FROM employees
GROUP BY username;

But the problem here is having maximum id doesn't always mean that is the active user (if there is one) has always the maximum id as you can see in the user SLEE.

I need to write a conditional join, if there is a user with employee status 3 return that otherwise return maximum id. Something like this query:

SELECT userid
    , IF(employeestatus = 3 THEN id ELSE MAX(id) END)
FROM employees
GROUP BY userid;

But this query doesn't work. Expected result is:

id          username   employee_status
33189       SLEE       3
29097305    AARM       0

Upvotes: 0

Views: 92

Answers (4)

hoangnh
hoangnh

Reputation: 249

I think this should work for you

SELECT (CASE
            WHEN max(status) = 0 THEN max(id)
            WHEN max(status) = 3 THEN
                   (SELECT id
                    FROM A a2
                    WHERE status = 3
                      AND a1.name = a2.name)
        END) AS aggregate_id,
       name
FROM A a1
GROUP BY name

DBFiddle

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

If you want the id where the status is 3 and the max id otherwise, then you can use conditional aggregation:

SELECT userid, MAX(employeestatus) as employee_status,
       COALESCE(MAX(CASE WHEN employeestatus = 3 THEN id END),
                MAX(id)
               )
FROM employees
GROUP BY userid;

Upvotes: 0

Suresh Gajera
Suresh Gajera

Reputation: 362

If you are using MySQL version 8.0+ then this code will work for you.

    SELECT ID,username,Employee_Status FROM 
    (
        SELECT *,ROW_NUMBER() OVER (PARTITION BY username ORDER BY Final_ID DESC) Rw_ID FROM 
        (
            SELECT ID,username,employee_Status,CASE WHEN Employee_Status = 3 THEN  2 ELSE 0 END + CASE WHEN R_ID = 1 THEN 1 ELSE 0 END AS Final_ID
            FROM 
                (
                    SELECT ROW_NUMBER() OVER (PARTITION BY username ORDER BY CAST(Id AS INT) DESC) AS R_ID ,* 
                    FROM  employees
                )A
        )A
    ) B WHERE Rw_ID = 1

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use conditional ordering with correlated sub-query : 

select e.*
from de_ie_cm.employees e
where e.id = (select e1.id
              from de_ie_cm.employees e1
              where e1.userid = e.userid
              order by (case when employeestatus = 3 then 0 else 1 end), id desc
              limit 1
             );

Upvotes: 1

Related Questions