Re: Duplicate Rows

From: David Portas <REMOVE_BEFORE_REPLYING_dportas_at_acm.org>
Date: Sat, 26 Jul 2008 10:24:04 +0100
Message-ID: <sb2dnbHVnLapcBfVnZ2dnUVZ8vWdnZ2d@giganews.com>

<artmerar_at_yahoo.com> wrote in message
news:67fb7670-5142-440f-9cc3-33c9ee914ebc_at_c65g2000hsa.googlegroups.com...
>
> Hi,
>
> I figured I'd ask this too. I know how to remove duplicate rows from
> a table, but this example used an analytical function. I'm new to
> these and do not get a huge chance to use them, so I'm always
> forgetting how they work.
>
> Please see this:
>
> select * from customer
> where rowid in
> (select rowid from
> (select
> rowid,
> row_number()
> over
> (partition by customer_id order by customer_id ) dup
> from customer)
> where dup > 1);
>
> Ok, that did show me the duplicates, but what if a duplicate was made
> up of more than 1 column? Can I still use an analytical function?
>
> This is where I might learn about these things......
>
> Thank you.

The OVER clause accepts a list of columns:

  SELECT * FROM
   (SELECT customer_id, col2, col3,
     ROW_NUMBER()
    OVER

     (PARTITION BY customer_id, col2, col3
      ORDER BY customer_id, col2, col3 ) dup

    FROM customer) t
  WHERE dup > 1;

-- 
David Portas

Received on Sat Jul 26 2008 - 04:24:04 CDT