Re: Query for finding duplicate rows
select col1, col2, count(*) from tab1
group by col1, col2
having count(*) > 1
Mickey Mestel <mickm@ix.netcom.com> on 17/03/2000 03:05:42
Please respond to Mickey Mestel <mickm@ix.netcom.com>
To: informix-list@iiug.org
cc: (bcc: Sue Davidson/EXE)
Subject: Query for finding duplicate rows
hi,
i need a query to find duplicate rows in a table, duplicate by a few of
the columns. i don't remember it exactly. the one i came up with gives
too much info. if there are more than one row with identical columns i
get the join of all of those, which is way too much. what i'm using is:
select a.rowid from tab1 a, tab1 b
where a.col1 = b.col1 and
a.col2 = b.col2 and
a.rowid != b.rowid
i need some other filtering factor in there though, so that for a given
row, even though there are many duplicates, i only get a single list of
duplicates, not a list of duplicates for *each* row.
does that make sense?
i have very limited access to the newsgroup, if you can mail to
mickm@baygate.com, i would appreicate it.
thanks,
mickm
--
-----------------------------------------------------------------------
This is a signature file. This is only a signature file. Had this
been an actual piece of useful information, you would have been
instructed on what to do with it.
-----------------------------------------------------------------------