Cloud Stack Ninja

I have below sample tables.

enter image description here enter image description here

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;

enter image description here

Once the records are grouped and ordered within each group using employee_reg_date, records should be populated as below for the group "101".

enter image description here

Then, I need to filter records in groups based on the following conditions.

  1. Take the first record of a group and check the status as Promoted. Then it is considered a valid record.
  2. 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)

enter image description here

How can I filter records based on these condtions using MYSQL



Read more here: https://stackoverflow.com/questions/64419531/iterate-the-records-in-groups-and-filter-only-records-based-criterias

Content Attribution

This content was originally published by Vinee-the-Pooh at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: