locked
help needed i RRS feed

  • Question

  • hi,

    consider the following table with data as:

    select * from test_group

    a    b   c   d
    12 14 15 faz
    12 14 15 raz
    13 15 16 shi
    13 15 16 ruv
    14 16 17 ram
    14 16 17 rev
    15 17 18 ros
    15 17 18 kis

    I need a query to get the result as:

     a   b    c   d
    12 14 15 faz
    13 15 16 shi
    14 16 17 ram
    15 17 18 ros

    plz help me in finding this query.....

     

    thanks

     

    • Changed type Ramna Friday, May 27, 2011 5:53 AM
    Friday, May 27, 2011 5:40 AM

Answers

  • ;With CTE 
    As
    (
    Select 
    * 
    ,Row_Number() Over (Partition by a, b, c order by d) As rn 
    From 
    test_group
    )
    
    Select a, b, c, d from CTE where RN = 1
    

    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    • Proposed as answer by Hasham NiazEditor Friday, May 27, 2011 8:32 AM
    • Marked as answer by KJian_ Thursday, June 2, 2011 7:21 AM
    Friday, May 27, 2011 5:45 AM
  • hi,

    try this

    ;with cte as
    (
    select *,ROW_NUMBER()over(partition by a,b,c order by (select 1)) as r from tablename
    )

    select * from cte where r=1

    @Ramna--> change the thread type to question.


    Thanks and regards, Rishabh , Microsoft Community Contributor
    • Marked as answer by KJian_ Thursday, June 2, 2011 7:21 AM
    Friday, May 27, 2011 5:49 AM

All replies

  • ;With CTE 
    As
    (
    Select 
    * 
    ,Row_Number() Over (Partition by a, b, c order by d) As rn 
    From 
    test_group
    )
    
    Select a, b, c, d from CTE where RN = 1
    

    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    • Proposed as answer by Hasham NiazEditor Friday, May 27, 2011 8:32 AM
    • Marked as answer by KJian_ Thursday, June 2, 2011 7:21 AM
    Friday, May 27, 2011 5:45 AM
  • hi,

    try this

    ;with cte as
    (
    select *,ROW_NUMBER()over(partition by a,b,c order by (select 1)) as r from tablename
    )

    select * from cte where r=1

    @Ramna--> change the thread type to question.


    Thanks and regards, Rishabh , Microsoft Community Contributor
    • Marked as answer by KJian_ Thursday, June 2, 2011 7:21 AM
    Friday, May 27, 2011 5:49 AM