In Postgres I have two tables:

Table A { int keyA, Text name}
Table B { int keyB, int keyA, char mark, date start, date end}

Mark from Table B could be 'X', 'Y', 'Z'. I want to get every record 'X' with dates but only one from 'Y', 'Z'. Also if there are 'X', 'Y', 'Z' i want only 'X'.

From:

I want to get

I tried: 1.

Select A.name, 
(select b2.start from B b2 where b2.keyA = A.keyA and b2.mark = 'X') as Start,
(select b2.end from B b2 where b2.keyA = A.keyA and b2.mark = 'X') as End,
from A order by name;

Order is important. I need to have name first. There is a porblem. In subqueries i have more than one record so i have to add limit 1. But I want to get every X not only one.

If I do this

 Select A.name, B.start, B.end
 from A inner join B on A.keyA = B.keyB

I'll have X, Y, Z and as I mentioned I want only X or one from Y or Z. Any idea how should I solve this?

CodePudding user response：

Use the row_number function with your join query as the following:

select name, keyB, keyA, mark, start_dt, end_dt
from
(
  select A.name, B.*,
    row_number() over (partition by B.keyA order by case when B.mark='X' then 1 else 2 end, B.keyb) rn
 from tableB B join tableA A
 on B.keyA = A.keyA
) T
where mark = 'X' or rn = 1
order by keyb

See demo