SQL Server Performance

Selecting distinct rows

Discussion in 'ALL SQL SERVER QUESTIONS' started by gopal, Jun 21, 2012.

  1. gopal Member

    Hello,
    I need some help on this please. I have a table with the following values.

    Table Name: Demo
    Id Fname Lname DOB Sex
    10 Robert Todd 1901-03-13 M
    10 Robert Todd 1903-03-13 M
    11 SAGER MUGESH 1920-03-23 M
    12 TOOHIG JANET 1928-08-24 F
    12 TOOHIG JANET 1928-08-24 F
    13 Biss Rob 1985-05-26 M

    The output should be like this....
    11 SAGER MUGESH 1920-03-23 M
    12 TOOHIG JANET 1928-08-24 F
    13 Biss Rob 1985-05-26 M
    I need to get a unique columns. if there is a data discrepancy within the ids i should ignore it. In the example above Id # 10 has data discrepancy (dob mismatch)

    I hope someone can help me.

    Thanks.
  2. gopal Member

    Nevermind. I got this working. Thank you.
  3. Shehap MVP, MCTS, MCITP SQL Server

    You might use the below query to figure out contradicted records you might use Row_number with partitionby commands as below:

    ;

    withTempas

    (selectROW_NUMBER()over (partitionbyId,Fname,lname,bob,sexorderbyID)asranknofromusers)

    Deletefromtempwhereranksno>1

    ;

    withTempas

    (selectROW_NUMBER()over (partitionbyId,Fname,lname,sexorderbyID)asrankno,idfromusers)

    Deletefromdemowhereidin(selectidfromtempdbwhererankno>1)

    Kindly please work out them and let me know your feedback

Share This Page