select c.name, r.containerYard.code, r.containerYard.address, (select max(ct1.number) from rtneo$Contract ct1 where ct1.mainContract is null and ct1.contragent.id = c.id) as mainContract, ct.number from rtneo$Contragent c, rtneo$Contract ct, rtneo$RealEstateContainerYard r

where c.id = ct.contragent.id

and c.id = r.contragentRealEstate.contragent.id

and ct.accepted = true

and ct.acceptanceDate = (select max(contract.acceptanceDate) from rtneo$Contract contract where contract.contragent.id = c.id)

and (select count(ct2) from rtneo$Contract ct2 where ct2.contragent.id = c.id and ct2.accepted = true and not ct2.mainContract is null) > 0

and (lower(c.legalAddress) like '%%г. байкальск%%' or lower(c.legalAddress) like '%%г байкальск%%')

order by c.inn