I have this table and I want to group it by these columns without duplicate: employee_id, level, effectivity_date.

| id | employee_id | level | approver_id | deleted_at | effectivity_date | created_at          | updated_at          |
|----|-------------|-------|-------------|------------|------------------|---------------------|---------------------|
| 25 | 13          | 1     | 50          | NULL       | 2022-02-16       | 2022-02-14 19:10:25 | 2022-02-14 19:10:25 |
| 24 | 13          | 3     | 27          | NULL       | 2022-02-14       | 2022-02-14 22:21:57 | 2022-02-14 22:21:57 |
| 23 | 13          | 2     | 6           | NULL       | 2022-02-14       | 2022-02-14 22:21:05 | 2022-02-14 22:21:05 |
| 22 | 13          | 1     | 8           | NULL       | 2022-02-14       | 2022-02-14 22:05:33 | 2022-02-14 22:05:33 |
| 21 | 13          | 1     | 4           | NULL       | 2022-02-14       | 2022-02-14 22:03:59 | 2022-02-14 22:03:59 |
| 20 | 27          | 1     | 48          | NULL       | 2022-02-14       | 2022-02-14 19:50:58 | 2022-02-14 19:50:58 |
| 19 | 8           | 1     | 27          | NULL       | 2022-02-14       | 2022-02-14 19:43:58 | 2022-02-14 19:43:58 |
| 17 | 13          | 1     | 8           | NULL       | 2022-02-14       | 2022-02-14 18:30:44 | 2022-02-14 18:31:34 |
| 16 | 13          | 1     | 44          | NULL       | 2022-02-16       | 2022-02-14 18:30:25 | 2022-02-14 18:30:25 |

This is my current SQL code progress, I don't know if this is the right way in doing this.

SELECT
    t1.employee_id,
    t1.level,
    t1.effectivity_date,
    t1.created_at
FROM leave_approvers t1
WHERE t1.employee_id = 13
AND (
    t1.employee_id,
    t1.level,
    t1.effectivity_date
) = ANY (
    SELECT 
        t2.employee_id,
        t2.level,
        t2.effectivity_date
    FROM leave_approvers t2
    WHERE t2.effectivity_date <= '2022-02-16'
    GROUP BY t2.employee_id, t2.level, t2.effectivity_date
)
ORDER BY t1.level, t1.effectivity_date ASC, t1.created_at ASC

Result of the above code, I don't want duplicate rows.

| employee_id | level | effectivity_date | created_at          |
|-------------|-------|------------------|---------------------|
| 13          | 1     | 2022-02-14       | 2022-02-14 18:30:44 |
| 13          | 1     | 2022-02-14       | 2022-02-14 22:03:59 |
| 13          | 1     | 2022-02-14       | 2022-02-14 22:05:33 |
| 13          | 1     | 2022-02-16       | 2022-02-14 18:30:25 |
| 13          | 1     | 2022-02-16       | 2022-02-14 19:10:25 |
| 13          | 2     | 2022-02-14       | 2022-02-14 22:21:05 |
| 13          | 3     | 2022-02-14       | 2022-02-14 22:21:57 |

Result that I want:

| employee_id | level | effectivity_date | created_at          |
|-------------|-------|------------------|---------------------|
| 13          | 1     | 2022-02-16       | 2022-02-14 19:10:25 |
| 13          | 2     | 2022-02-14       | 2022-02-14 22:21:05 |
| 13          | 3     | 2022-02-14       | 2022-02-14 22:21:57 |

CodePudding user response：

If your MySQL version didn't support window function, you can try to use a subquery to get MAX effectivity_date then do self-join

Query #1

SELECT t1.employee_id,
    t1.level,
    t1.effectivity_date,
    max(t1.created_at) created_at
FROM leave_approvers t1
INNER JOIN (
    SELECT level,employee_id,MAX(effectivity_date) max_effectivity_date
    FROM leave_approvers
    GROUP BY level,employee_id
) t2 
ON  t1.effectivity_date = t2.max_effectivity_date
AND t1.level = t2.level
AND t1.employee_id = t2.employee_id
WHERE t1.employee_id = 13
GROUP BY t1.employee_id,
    t1.level,
    t1.effectivity_date
ORDER BY t1.level, t1.effectivity_date ASC, max(t1.created_at) ASC;

View on DB Fiddle