Home How do I get values based on evaluation of only one column values
 Table 1  Dept ID Name EmpID Attended Sub Dept 1 Rob 100 N HR 1 Max 101 N Mech 1 Mary 102 Y IT 2 Jay 103 N Mech 2 Jon 104 Y IT 2 Jim 105 N IT 2 Jack 106 N HR 2 Sam 107 Y IT 2 Sim 108 N Eng 2 Abs 109 Y IT 2 Name 110 N IT 3 Phil 111 Y IT 3 Pam 112 N IT 3 Pen 113 Y IT  Expected Output  Dept ID Name EmpID Attended Sub Dept 1 Rob 100 N HR 1 Max 101 N Mech 2 Jay 103 N Mech 2 Jim 105 N IT 2 Jack 106 N HR 2 Sam 107 Y IT 2 Sim 108 N Eng 3 Pam 112 N IT  IT sub dept within a Dept ID is used to compare and get the rows with attended flag. So the scenario is I need to look at attended column based on Dept ID, and take only those records where attended = Y. Note, Attended = Y will only exist for IT but not all IT will have Attended = Y. After that, I need to look Attended = Y or N for IT and then get the records. Emp ID is the order they receive per dept ID and the way record come in. For ID 1 there was one record = Y so we should get 2 records before that. For ID = 2, Emp ID = 104 had first = Y , we need to get 103 emp ID, then we evaluate other rows , as we can see 104 and 105 both have IT (but only 104 had Y, so comparing to the next row 105 we don't need 104, and start our evaluation from 105 to another ID having IT. 107 has IT and attended = Y, we need to get 105 and 106. Then we evaluate 107 with another id with ID, which is 109, so we get 107 and 108. Then for 109 we need to look at another row with IT, as for the next row we don't have IT and attended = Y, we don't need to consider 109 and start at 110 if any rows are added for the Dept ID. Same logic applies for DeptID = 3,