dcsimg
Results 1 to 2 of 2

Thread: finding dups

  1. #1
    Join Date
    Nov 2011
    Posts
    1

    finding dups

    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

  2. #2
    Join Date
    Sep 2005
    Posts
    168
    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--

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •