SQL Query

Hello,

I am using a sql query but records are repeat in more time because using a column name I.im_ondate(repeat same date in table).

I want choses Max date with only one field/records (in column I.im_ondate) from table I.

My Query is

 Select  a.Comp_id,a.comp_title,a.comp_datecreated,a.comp_relief,b.sec_name,c.seg_name,
d.brand_name,a.comp_status,a.comp_closedreason,a.ClosedDAte,a.ResolvedDate,R.br_message,
MAX(DISTINCT R.br_ondate) AS MessageDate,I.im_messagedetails,MAX(DISTINCT I.im_ondate) AS MessageDate1

from tbl_complaints a join tbl_mst_sectors b
on a.sec_id=b.sec_id join tbl_mst_segments c
on a.seg_id=c.seg_id join TestBrandMaster d
on a.b_id=d.b_id
join tbl_usercontacts e on a.u_uid=e.u_uid
join tbl_brandinfo F on a.b_id=F.b_id
join tbl_userlogin U on a.u_uid=U.u_uid
join tbl_brandreplies R on a.comp_id=R.comp_id
join tbl_inboxmessages I on a.comp_id=I.comp_id
where a .comp_datecreated >='2010-01-01' and a.comp_datecreated <='2010-04-01'

Group by a.Comp_id,a.comp_title,a.comp_datecreated,a.comp_relief,b.sec_name,c.seg_name,
d.brand_name,a.comp_status,a.comp_closedreason,a.ClosedDAte,a.ResolvedDate,R.br_message,R.br_ondate
,I.im_messagedetails,I.im_ondate
order by comp_datecreated

---

Thank's

Try Max function with Over() clause.

SELECT a.comp_id, 
       a.comp_title, 
       a.comp_datecreated, 
       a.comp_relief, 
       b.sec_name, 
       c.seg_name, 
       d.brand_name, 
       a.comp_status, 
       a.comp_closedreason, 
       a.closeddate, 
       a.resolveddate, 
       r.br_message, 
       MAX(DISTINCT r.br_ondate) OVER() AS messagedate, 
       i.im_messagedetails, 
       MAX(DISTINCT i.im_ondate) OVER() AS messagedate1 
FROM   tbl_complaints a 
       JOIN tbl_mst_sectors b 
         ON a.sec_id = b.sec_id 
       JOIN tbl_mst_segments c 
         ON a.seg_id = c.seg_id 
       JOIN testbrandmaster d 
         ON a.b_id = d.b_id 
       JOIN tbl_usercontacts e 
         ON a.u_uid = e.u_uid 
       JOIN tbl_brandinfo f 
         ON a.b_id = f.b_id 
       JOIN tbl_userlogin u 
         ON a.u_uid = u.u_uid 
       JOIN tbl_brandreplies r 
         ON a.comp_id = r.comp_id 
       JOIN tbl_inboxmessages i 
         ON a.comp_id = i.comp_id 
WHERE  a .comp_datecreated >= '2010-01-01' 
       AND a.comp_datecreated <= '2010-04-01' 
GROUP  BY a.comp_id, 
          a.comp_title, 
          a.comp_datecreated, 
          a.comp_relief, 
          b.sec_name, 
          c.seg_name, 
          d.brand_name, 
          a.comp_status, 
          a.comp_closedreason, 
          a.closeddate, 
          a.resolveddate, 
          r.br_message, 
          r.br_ondate, 
          i.im_messagedetails, 
          i.im_ondate 
ORDER  BY comp_datecreated