Mysql query with one or more rows one status takes precedence Mysql 5.7

user_2680315 0
Viewed 58

I have the following fields:

| employee_email | status | id |
| test1@test.com |  active | 1 | 
| test1@test.com |  terminated | 2 |
| test2@test.com |  active | 3 |
| test3@test.com |  terminated | 4 |
| test4@test.com |  terminated | 5 |
| test4@test.com |  terminated | 6 |

I need to query with a rule that if there are more than 1 rows for the same employee_email (and one is active and one is terminated - then return just the row = active). IF more than 1 row and both are terminated, then return just one row that is terminated.

Output should be:

| employee_email | status | status |
| test1@test.com |  active | 1 |
| test2@test.com |  active | 3 |
| test3@test.com |  terminated | 4 |
| test4@test.com |  terminated | 5 |
mysql
1 Answers

If your version of MySql is 8.0+ you can use window function ROW_NUMBER():

SELECT t.employee_email, t.status, t.id
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY employee_email ORDER BY status = 'active' DESC, id) rn
  FROM tablename
) t
WHERE t.rn = 1

For previous versions:

SELECT t.employee_email, MAX(t.status) status, MIN(t.id) id
FROM tablename t
WHERE t.status = 'active'
OR NOT EXISTS (SELECT 1 FROM tablename WHERE employee_email = t.employee_email AND status = 'active')
GROUP BY t.employee_email

See the demo.
Results:

> employee_email | status     | id
> :------------- | :--------- | -:
> test1@test.com | active     |  1
> test2@test.com | active     |  3
> test3@test.com | terminated |  4
> test4@test.com | terminated |  5
user_104988280
Related