Printable View
Hi,
I am trying to find dups as follows:
Picid ddate price
1 9/1/2011 40.00
1 9/2/2011 40.00
1 9/3/2011 50.00
1 9/4/2011 50.00
2 9/4/2011 60.00
2 9/6/2011 60.00
So, in the example above, I want row #'s 2, 4 and 6 to be identified as dups as they have the same Picid, and price, but the date changed within the Picid
declare @t table(id int identity(1,1), picid int, ddate datetime, price money)
insert into @t(picid, ddate, price)
values (1, '20110901', 40), (1, '20110902', 40), (1, '20110903', 50), (1, '20110904', 50), (2, '20110904', 60), (2, '20110906', 60), (1, '20110901', 40)
select *
from
(
select *, dense_rank() over(partition by picid, price order by ddate) as denseranking
from @t
) AS t
--where denseranking > 1
--HTH--