SELECT  -- assoc.id,comc.id as i,
 Max (comcd.id ) as comcdid, p.name1,comcp.effectivedate,sub.description as subbranch,cov.description as Kalipseis,
( select max(commist.longdesc)) as Commision,
(select max(comcd.comcontractperiodid)) as code,
cov.id as coverid, sub.id as subid
into		#temp1
FROM		       PERSON p INNER JOIN
                      ASSOCCOMCONTRACTS assoc ON p.ID = assoc.PERSONID INNER JOIN
                      COMCONTRACTS comc ON assoc.COMCONTRACTSID = comc.ID INNER JOIN
                      COMCONTRACTDETAILS comcd ON comc.ID = comcd.COMCONTRACTID INNER JOIN
                      COMMISSIONTREATY commist ON comcd.COMMISSIONTREATYID = commist.ID INNER JOIN
                      SUBBRANCHCOVERS subc ON comcd.SUBBRANCHCOVERSID = subc.ID INNER JOIN
                      SUBBRANCH sub ON subc.SUBBRANCHID = sub.ID  INNER JOIN
                      COVERSLIB cov ON subc.COVERSLIBID = cov.ID inner join
                      assoclevels assocl  ON assoc.ASSOCLEVELSCODEID = assocl.CODEID inner join
                      COMCONTRACTPERIOD comcp ON comc.ID = comcp.COMCONTRACTID








 where --p.name1 like '???????S%'
   p.isbroker =1
 and sub.id in (2,17,126,127,37,125,25,26)
 --and sub.id =126
and comcd.effectivedate is null
and cov.id in (1,9,13,23,29,59,272,100,279,97,98)
 and assocl.codeid=0
and comcp.effectivedate = (select max(comcp1.effectivedate) from  PERSON p1 INNER JOIN
                      ASSOCCOMCONTRACTS assoc1 ON p1.ID = assoc1.PERSONID INNER JOIN
                      COMCONTRACTS comc1 ON assoc1.COMCONTRACTSID = comc1.ID INNER JOIN
                      COMCONTRACTDETAILS comcd1 ON comc1.ID = comcd1.COMCONTRACTID inner join
                      COMCONTRACTPERIOD comcp1 ON comc1.ID = comcp1.COMCONTRACTID

                      where p1.id =p.id )
group by name1,comcp.effectivedate,cov.description,commist.longdesc,sub.description,cov.id,sub.id
order by p.name1,sub.description



SELECT	--	w.comcdid,
		w.name1,
		w.effectivedate,
		w.name1,
		w.subbranch,
		w.Kalipseis,
		w.Commision
--		w.code,
-- w.coverid,	
-- w.subid

	


from	#temp1 as w

where w.comcdid = 
CASE w.subid 

         WHEN 126  THEN (select max(comcdid) from #temp1 g where g.coverid=w.coverid)
            ELSE (select distinct comcdid from #temp1 g where g.comcdid=w.comcdid)
      END


--(select max(comcdid) from #temp1 g where g.coverid=w.coverid)
---enipothiko mono
--and g.subid <>126) 


drop table #temp1