Forum Stats

  • 3,733,959 Users
  • 2,246,849 Discussions
  • 7,856,951 Comments

Discussions

How to avoid duplicate values in my query

kolipaka
kolipaka Member Posts: 210
edited May 2015 in SQL

Hi Folks,

I have written one query which retrieves data as per filters, but here I am facing issue becasue, in my TABLE_NAME1 having two columns SEQ_ID, SOURCEID, these two columns having deplicate values.

Now I want to eliminate duplicates by grouping both columns.

Sample data:

seq_id     sourceid

100               123

100               123

200               234

200               234

300               127

300               127

400               254

400               254

here in sample data for seq_id = 100, needs to fetch one record, if sourceid dif then display that as well.

my query:

SELECT

A.CUSTID AS A_CUSTID,B.CUSTID AS B_CUSTID,A.OBS_TS AS A_OBS_TS,B.OBS_TS AS B_OBS_TS, A.SOURCEID AS A_SOURCEID,B.SOURCEID AS B_SOURCEID, A1.LAST_UPDT_DTM AS A_LAST_UPDT_DTM,

B1.LAST_UPDT_DTM AS B_LAST_UPDT_DTM, A1.SRC_PRC_STTS_CD AS A_SRC_PRC_STTS_CD,B1.SRC_PRC_STTS_CD AS B_SRC_PRC_STTS_CD

FROM(SELECT      CUSTID,SOURCEID,OBS_TS

FROM TABLE_NAME1 X

WHERE   EXISTS   (

SELECT 1

FROM(

SELECT  CUSTID,  MAX(OBS_TS) AS OBS_TS

FROM     TABLE_NAME1

WHERE OBS_TS IS NOT NULL

GROUP BY CUSTID) Y

WHERE Y.CUSTID=X.CUSTID AND Y.OBS_TS=X.OBS_TS

)  ) A

INNER JOIN

(  SELECT      CUSTID,SOURCEID,OBS_TS

   FROM TABLE_NAME1 P

   WHERE   EXISTS   (

  SELECT 1

   FROM

  ( SELECT  CUSTID,  MAX(SOURCEID)  AS SOURCEID

     FROM     TABLE_NAME1

     WHERE OBS_TS IS NOT NULL

    GROUP BY CUSTID

  ) Q  WHERE P.CUSTID=Q.CUSTID AND P.SOURCEID=Q.SOURCEID

  )  ) B

    ON  A.CUSTID=B.CUSTID

   AND A.SOURCEID<>B.SOURCEID

LEFT OUTER JOIN

SRC_REC_CNT  A1

ON  A.SOURCEID =A1.SOURCE_ID

AND  A1.SEQ_ID = 100 AND A1.CODE ='CUSTOMER' AND  A1.OBJ_ID=1

LEFT OUTER JOIN

SRC_REC_CNT  B1

ON  B.SOURCEID =B1.SOURCEID

AND  B1.SEQ_ID =100 AND B1.CODE='CUSTOMER' AND  B1.OBJ_ID=1;

can you please suggest me how can I avoid duplicate .

Regards,

Lakshman

Sign In or Register to comment.