+-------------+------------------+----------+---------------+--------------+ |Id |EmployeePositionId|Date |Time |TypeEmployment| +-------------+------------------+----------+---------------+--------------+ |4399 |4557 |2022-01-10|60 |0 | |4399 |4557 |2022-01-10|480 |0 | |4399 |4561 |2022-01-10|540 |1 | |4399 |4559 |2022-01-10|540 |2 | |2448 |3017 |2022-01-31|480 |0 | |2448 |3017 |2022-01-28|480 |0 | |3406 |3841 |2022-01-31|480 |1 | |3406 |3841 |2022-01-28|480 |1 | |3406 |3841 |2022-01-27|480 |1 | |3406 |3841 |2022-01-26|480 |1 | +-------------+------------------+----------+---------------+--------------+
Need to filter the records so that the employee mostly has only the position with the employment type 0.
I can’t just do where TypeEmployment = 0, because it will cut off the employee with private face id 3406
But I would like to see him, because he does not have a position with type 0
However, the employee with PrivateFaceId 4399 has three positions: 4557, 4561, 4559 (with type 0, 1, 2 respectively) and I would like to filter out those entries that do not match type 0
This is something like coalesce for rows, not columns. Which takes those first positions with type 0, if they are not, then with type 1, if they are not, then with type 2
Finally, i would like to see:
+-------------+------------------+----------+---------------+--------------+ |Id |EmployeePositionId|Date |Time |TypeEmployment| +-------------+------------------+----------+---------------+--------------+ |4399 |4557 |2022-01-10|60 |0 | |4399 |4557 |2022-01-10|480 |0 | |2448 |3017 |2022-01-31|480 |0 | |2448 |3017 |2022-01-28|480 |0 | |3406 |3841 |2022-01-31|480 |1 | |3406 |3841 |2022-01-28|480 |1 | |3406 |3841 |2022-01-27|480 |1 | |3406 |3841 |2022-01-26|480 |1 | +-------------+------------------+----------+---------------+--------------+
Thank you for any help
Advertisement
Answer
Order positions within the id as needed, take the first one
select *
from (
select *
, row_number() over(partition by Id order by TypeEmployment) rn
from mytable
) t
where rn=1
Use dense_rank() instead of row_number() if all positions with the same top TypeEmployment are required.