Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Getting first value in non distinct column

回答済み Getting first value in non distinct column

  • Friday, December 14, 2012 4:08 AM
     
     
    Sign In to Vote
    0

    Hi,

    I have table like

    CL1                CL2

    A                      1

    A                      2

    A                      3

    B                     11

    B                     14

    I just want like

    A                 1

    B                 11

    CL1 should be unique. Only one record per distinct CL1 column.

    I tried with different sub queries and all, they're not working. How to solve this problem.

     

All Replies

  • Friday, December 14, 2012 4:43 AM
     
      Has Code
    Sign In to Vote
    0

    Pasupathi, Try as follows using CTE, this will work on SQL Server 2005 and above

    Create table test(cl1 char(1), cl2 int)

Insert into test values('A',1),('A',2),('A',3),('A',1),('B',11),('B',14)


WITH temp AS
(
 SELECT *,
  ROW_NUMBER() OVER(PARTITION BY cl1 ORDER BY cl2) AS rn
 FROM test 
 )
 select cl1,cl2 from temp where rn=1


    Thanks
    Manish

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

     
  • Friday, December 14, 2012 4:45 AM
     
     Answered
    Sign In to Vote
    0

    Here I found the answer.

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/3aceee1d-3f7b-42b3-9bb0-7142618c407b

    • Marked As Answer by Pasupathi Friday, December 14, 2012 4:46 AM
    •