Need to remove duplicate records and get distinct values

  • Hi

    I am sorry if someone has already posted this question and am asking again.

    This problem is eating my head, I have a table

    Table 1 -

    1 br

    1 er

    2 rt

    3 yh

    2 uj

    3 iu

    3 ol

    Now I want only 1 row for each unique id, that row could be any row but id shouldn't repeat.

    1 br

    2 rt

    3 yh

    or

    1 er

    2 uj

    3 ol

    Thanks

  • Here are a few ways...

    select a, min(b) as b from [Table 1] group by a

    select a, max(b) as b from [Table 1] group by a

    ; with x as (select *, row_number() over (partition by a order by a) as row from [Table 1])

    select a, b from x where row = 1

    Use this to physically delete 'duplicate' data from your table...

    ; with x as (select *, row_number() over (partition by a order by a) as row from [Table 1])

    delete from x where row > 1

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • DECLARE @T Table (ID int , name varchar(50))

    INSERT INTO @T

    Select 1,'br' UNION

    Select 1,'er' UNION

    Select 2,'rt' UNION

    Select 3,'yh' UNION

    Select 2,'uj' UNION

    Select 3,'iu' UNION

    Select 3,'ol'

    Select ID,Name From

    (SELECT ID,Name,Row_Number() OVER ( Partition By ID Order By Name) Rank FROM @T) As Z

    Where Rank = 1

  • Thanks for the replies!

    It worked.

    Is there any way to do this without the OVER clause?

    Also can you also suggest me a good article on the window aggregate functions. The OVER clause is not completely known to me.

    Thanks!

  • Hi Sanchita,

    BOL is best to learn about that.

    Just type the keyword in INDEX of BOL and see the explaination.

  • Thanks!

    Well we have found another way to do the same without OVER

    select * from testtable

    select distinct id, (select top 1 datavalue from testtable where id = t1.id) from testtable t1

  • Even you can use the query suggested by Ryan

    select a, min(b) as b from [Table 1] group by a

    select a, max(b) as b from [Table 1] group by a

  • Yes absolutely.

    Thanks to both of you!

  • Nice logic hari..

    Thanks for posting the code.....

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply