I have below sample tables.
First, I need to group records based on employee_id as below and order the records by employee_reg_date within each group. And I did it using this query. But I am not able to order the grouped records. As an example for "101" group, 2020-01-01 records should be populated as the first record.
SELECT e1.* FROM e e1 JOIN (SELECT emplyee_dep_id,COUNT(*) FROM e GROUP BY emplyee_dep_id HAVING count(*) > 1 ) e2 ON e1.emplyee_dep_id = e2.emplyee_dep_id;
Once the records are grouped and ordered within each group using employee_reg_date, records should be populated as below for the group "101".
Then, I need to filter records in groups based on the following conditions.
- Take the first record of a group and check the status as Promoted. Then it is considered a valid record.
- Take the second record, if the status is Promoted, it is not considered as a valid record.
Basically, If the current record is Promoted in a group but just before the immediate record is promoted within the same group, current record is invalid for the group.
And, If the current record is Promoted in a group but just before the immediate record is probation within the same group, current record is valid for the group.
Based on this logic, Only valid records are for this "101" group, (Highlighted records in yellow)
How can I filter records based on these condtions using MYSQL