sql server 2005 - Remove duplicate row and update next row to current row and continue -

sql server 2005 - Remove duplicate row and update next row to current row and continue -

i need select query ..

environment : sql dba -sql server 2005 or newer

example : in sample table, if select top 20 no duplicate records should come , next record should in 20 records .

example :

123456 should not repeat in 20 records , if 18th duplicate, in place of 18th, 19th record should come , in 19th—20th should come, in 20th ---21st should come .

no concern of asc or desc rows .

lookup table before

id name 123456 hello 123456 hello 123654 hi 123655 yes

lookup table after

id name 123456 hello 123654 hi 123655 yes

my table:

create table [dbo].[test]( [id] [int] identity(1,1) not null, [contestid] [int] not null, [prizeid] [int] not null, [contestparticipantid] [int] not null, [subsidiaryanswer] [varchar](256) not null, [subsidiarydifference] [bigint] not null, [attempttime] [datetime] not null, [participantname] [varchar](250) not null, [issubscribed] [bit] not null, [isnewlyregistered] [bit] not null, [iswinner] [bit] not null, [iswinnerconfirmed] [bit] not null, [iswinnerexcluded] [bit] not null) on [primary]

my question is: select, need first 20, unique ones.

select top 20 * test order subsidiarydifference

when above query, have double in there. in case there double, need take them 1 time , take next one

any 1 know issue ?

thanks in advance :)

reading question, appears don't want delete rows table - want display top 20 distinct rows - seek this:

;with lastpercontestparticipantid ( select contestparticipantid, -- add together whatever other columns want select here row_number() over(partition contestparticipantid order subsidiarydifference) 'rownum' dbo.test ) select top (20) contestparticipantid, -- add together whatever other columns want select here subsidiarydifference lastpercontestparticipantid rownum = 1

this show most recent row each distinct contestparticipantid, order subsidiarydifference - seek it!

update #2: i've created quick sample - uses info original post - plus additional subid column can order rows of same id something...

when run cte query, 1 entry each id - "not working" you?

declare @test table (id int, entryname varchar(50), subid int) insert @test values(123456, 'hello', 1), (123456, 'hello', 2), (123654, 'hi', 1), (123655, 'yes', 3) ;with lastperid ( select id, entryname, row_number() over(partition id order subid desc) 'rownum' @test ) select top (3) id, entryname lastperid rownum = 1

gives output of:

id entryname 123456 hello 123654 hi 123655 yes

no duplicates.

sql-server-2005 select