none
Query

    Question

  • Hi

    Table A

    No      Name        Acct

    1        King          2256

    2                         2256

    3        Lisa           1111

    4        Lisa           1111

    5                          2222

    6                          2222

    7                          8888

    8       Linda          8888

    I need to write a query to get the duplicate records based on acct field from table A.

    conditions :

    * Select the record with name is not null.  Eg . Row 1 and 2    .. The output should have row 1

    * If name is present in both records then select the top 1.   Eg : Row 3 and 4... The output should have row 3

    * If name is null in both rows then select the top 1.   Eg :row 5 and 6 .. The output should have row 5

    *Select the record with name is not null . Eg Row 7 and 8 . The output should have 8.

    Expected output.

    No      Name        Acct

    1        King          2256

    3        Lisa           1111

    5                          2222

    8       Linda          8888


    Please help

    Thursday, April 03, 2014 12:49 AM

Answers

  • Try the below:

    ;With cte as
    (
       Select *, Row_Number()Over(partition by Accnt order by Name desc,[No] asc) Rn
       From yourtablename
    ) Select * From cte where rn=1

    • Marked as answer by Cool Tech Thursday, April 03, 2014 1:35 AM
    Thursday, April 03, 2014 1:10 AM
  • Select * from 
    (select No, Name, Acct, row_number() Over(Partition by Acct Order by Name DESC) rn from test
    ) t
    WHERE rn=1
    

    • Marked as answer by Cool Tech Thursday, April 03, 2014 1:35 AM
    Thursday, April 03, 2014 1:16 AM
    Moderator

All replies

  • Try the below:

    ;With cte as
    (
       Select *, Row_Number()Over(partition by Accnt order by Name desc,[No] asc) Rn
       From yourtablename
    ) Select * From cte where rn=1

    • Marked as answer by Cool Tech Thursday, April 03, 2014 1:35 AM
    Thursday, April 03, 2014 1:10 AM
  • Select * from 
    (select No, Name, Acct, row_number() Over(Partition by Acct Order by Name DESC) rn from test
    ) t
    WHERE rn=1
    

    • Marked as answer by Cool Tech Thursday, April 03, 2014 1:35 AM
    Thursday, April 03, 2014 1:16 AM
    Moderator