Re: deleting duplicate rows

In article <1994Jul29.042203.59417_at_frodo.cc.flinders.edu.au>, carr_at_ist.flinders.edu.au (Matthew J Carr) writes:
|>I have a table consisting of 3 columns: thread_id, mess_id, length
|>I am trying to remove duplicate rows of thread_id and mess_id so only one copy remains
|>
|>Is it possible to construct a query using the rowid?
|>
|>e.g delete from th_table t1
|> where rowid > (select rowid from th_table
|> where thread_id = t1.thread_id
|> and mess_id = t1.mess_id
|> and rowid < t1.rowid)
|>
|>It returns an errror: ORA-01427: single-row subquery returns more than one row
|>
|>Can it be done this way????
|>
The solution is :

	delete from th_table t1
	where not t1.rowid = (select min(t2.rowid) from th_table t2
				where t2.thread_id = t1.thread_id);

-- 
Yves NOEL ______________________________________________ C.I.T.I. (batiment M4)
Database Administrator ________________ Universite des Sciences et Technologies
Email: Yves.Noel_at_univ-lille1.fr ________ 59655 Villeneuve d'Ascq Cedex - FRANCE
Voice: (33) 20.33.70.74 _________________________________ Fax: (33) 20.43.66.25

I use ORACLE v6.0.36 on DEC RISC ULTRIX v4.3
___