Re: Fundamental Question!!

From: Angelo Cavallaro <angelo.cavallaro_at_pcm.bosch.de>
Date: 1998/02/03
Message-ID: <34D6EC19.2C7D_at_pcm.bosch.de>#1/1

> Can somone tell me how to eliminate duplicate data
> 1.Using row_id concept

Delete from table a
 where a.rowid != (select b.rowid

		     from table b
		    where a.key_1 = b.key_1
		      and a.key_2 = b.key_2
		      and ...
		      and rownum = 1);

Where key_1, key_2 and so on are the columns, which you wish to be unique.

> If I want the third largest value in a column, what should my sql query
> be like.

Select max(a.value)
  from table a, table b, table c
 where a.value < b.value
   and b.value < c.value;

Or, if the first, second an third can be the same value:

Select max(a.value)
  from table a, table b, table c

 where a.value <= b.value
   and b.value <= c.value
   and a.rowid != b.rowid
   and b.rowid != c.rowid;

Received on Tue Feb 03 1998 - 00:00:00 CET