none
Finding duplicate records from two tables

    Question

  • I have  table T1 with more than 1000 Records and there are few dulpicates in this table T1


    SELECT segment, LandUseGroup, StateAbbreviation,Unit  FROM  T1
    WHERE EXISTS
    (
    SELECT  Sc_id, bMP_ID,segment, LandUseGroup,S.StateAbbreviation,U.Unit
    FROM T2 --- i have this table with results from joining few tables
    )


    i have to find if there are any duplicates with combination of segment, LandUseGroup, StateAbbreviation,Unit with the Bmp_id in T2

    Please suggest me

    Bottom line is :
    BMP_id record submitted as a percent( in T1)  do not overlap with any other records for that same BMP on the same landuse in the same Segment

     
    Wednesday, September 04, 2013 7:26 PM

Answers

  • ;with cte as
    (
     SELECT segment, LandUseGroup, StateAbbreviation,Unit  FROM  T1
    WHERE EXISTS 
    ( 
    SELECT  Sc_id, bMP_ID,segment, LandUseGroup,S.StateAbbreviation,U.Unit
    FROM T2 --- i have this table with results from joining few tables 
    )
    )
    
    
    
    select
    row_number() over (partition by segment, LandUseGroup, StateAbbreviation, Unit order by segment) RowNumber,
    segment,
    LandUseGroup,
    StateAbbreviation,
    Unit
    from cte
    
    


    Bonediggler

    Wednesday, September 04, 2013 8:10 PM

All replies

  • Have a look at this link:

    duplicate


    The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd


    My blog


    Wednesday, September 04, 2013 7:47 PM
  • ;with cte as
    (
     SELECT segment, LandUseGroup, StateAbbreviation,Unit  FROM  T1
    WHERE EXISTS 
    ( 
    SELECT  Sc_id, bMP_ID,segment, LandUseGroup,S.StateAbbreviation,U.Unit
    FROM T2 --- i have this table with results from joining few tables 
    )
    )
    
    
    
    select
    row_number() over (partition by segment, LandUseGroup, StateAbbreviation, Unit order by segment) RowNumber,
    segment,
    LandUseGroup,
    StateAbbreviation,
    Unit
    from cte
    
    


    Bonediggler

    Wednesday, September 04, 2013 8:10 PM