If there are several Positions, then find only the main one, otherwise take another

user_12619509 0
Viewed 39
+-------------+------------------+----------+---------------+--------------+
|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

group-by postgresql sql
2 Answers

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.

user_62199790

Two solutions.

The first uses a group by:

select * from Table1 where
("Id", "TypeEmployment") in
(select
 "Id", min("TypeEmployment")
from Table1
group by "Id")
order by 5, 2 desc, 4, 3 desc
;

The second uses a window function min:

WITH subq as (select
 *, min("TypeEmployment") over (partition by "Id") mini
from Table1)
select 
  "Id",
  "EmployeePositionId",
  "Date",
  "Time",
  "TypeEmployment"
from
  subq
where
  "TypeEmployment"="mini"
order by 5, 2 desc, 4, 3 desc
;

DDL:

CREATE TABLE Table1
    ("Id" int, "EmployeePositionId" int, "Date" timestamp, "Time" int, "TypeEmployment" int)
;
    
INSERT INTO Table1
    ("Id", "EmployeePositionId", "Date", "Time", "TypeEmployment")
VALUES
    (4399, 4557, '2022-01-10 00:00:00', 60, 0),
    (4399, 4557, '2022-01-10 00:00:00', 480, 0),
    (4399, 4561, '2022-01-10 00:00:00', 540, 1),
    (4399, 4559, '2022-01-10 00:00:00', 540, 2),
    (2448, 3017, '2022-01-31 00:00:00', 480, 0),
    (2448, 3017, '2022-01-28 00:00:00', 480, 0),
    (3406, 3841, '2022-01-31 00:00:00', 480, 1),
    (3406, 3841, '2022-01-28 00:00:00', 480, 1),
    (3406, 3841, '2022-01-27 00:00:00', 480, 1),
    (3406, 3841, '2022-01-26 00:00:00', 480, 1)
;

Output:

Id EmployeePositionId Date Time TypeEmployment
4399 4557 2022-01-10T00:00:00Z 60 0
4399 4557 2022-01-10T00:00:00Z 480 0
2448 3017 2022-01-31T00:00:00Z 480 0
2448 3017 2022-01-28T00:00:00Z 480 0
3406 3841 2022-01-31T00:00:00Z 480 1
3406 3841 2022-01-28T00:00:00Z 480 1
3406 3841 2022-01-27T00:00:00Z 480 1
3406 3841 2022-01-26T00:00:00Z 480 1
user_115822700
Related