Re: What is the fastest way to delete duplicate rows from a table?

Chris Brauer wrote: > > > select {keycols}, count(*) > > from atable > > group by {keycols} > > having count(*) > 1 > > into temp fred; > > > > delete from atable > > where rowid in ( > > select min(t.rowid) > > from atable t, fred f > > where {join conditions on {keycols} between t & f} > > group by {keycols} > > );> > Art S. Kagel > > That's funny, Art: every time you need a temp table (not worth to stay after > use) > you call it "Fred". Does that mean you had a guy in your childhood named Fred > you didn't like at all ? It's just a table called fred. It's just YOU who sees a guy from your childhood.... Would you like to talk about that? .... Because Art's shrink would be happy to listen! :-) > It would be funny to know every DBAs favourite "garbage-name"... :-) Now there's another negative term. To me, every file I create has a use, otherwise I wouldn't have created it. Therefore, none of my temporary files are garbage, ;-) > By the way: my garbage table sounds most of the time "jau" or "jepp" or > "jawoll"... ... Now this guy called Jau, or Jepp, or Jawoll who you knew from your childhood, and you think of as 'garbage'......, ... did he used to beat you up?..... :-)) Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk |//////// /| | http://www.informix.com http://www.informixhandbook.com |///// / //| | http://www.iiug.org +-----------------------------------+//// / ///| | |Dr. Stock is listening.... So his |/// / ////| | |eyes are closed & he's snoring, but|// / /////| | |that doesn't mean he doesn't care. |/ ////////| +----------------------+-----------------------------------+-----------+