Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2006
    Posts
    1

    Unanswered: finding dups based on prev rows

    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
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    try this

    create table #abc
    (
    Picid int,
    ddate datetime,
    price money
    )


    insert into #abc(picid, ddate, price)
    select 1, convert(datetime,'9/1/2011'), 40.00
    union
    select 1, convert(datetime,'9/2/2011'), 40.00
    union
    select 1, convert(datetime,'9/3/2011'), 50.00
    union
    select 1, convert(datetime,'9/4/2011'), 50.00
    union
    select 2, convert(datetime,'9/4/2011'), 60.00
    union
    select 2, convert(datetime,'9/6/2011'), 60.00



    With Abc (row#, picid, ddate, price)
    AS
    (select Row_number() over (partition by picid, price order by ddate) as row#,
    picid, ddate, price
    from #abc
    )
    select * from abc where row# > 1
    Cheers....

    baburajv

  3. #3
    Join Date
    Nov 2011
    Posts
    1
    you could try a group by on priceid and price with max(date)...

  4. #4
    Join Date
    Sep 2011
    Posts
    75
    Quote Originally Posted by pvdbforum View Post
    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
    SELECT Picid,ddate,price ROW_NUMBER OVER (ORDER BY Picid,ORDER BY ddate) as 'ROWNUM'

Posting Permissions

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