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.
-----------------------------------------------------------------------
Another approach that might work better:
select col1, col2, count(*) from tab1
group by col1, col2
having count(*) > 1
Untried, but I think I got the syntax right.
--
=======================================================
Dennis J. Pimple Informix Software, Inc.
Principal Consultant 6300 S Syracuse Way Ste 205
dennisp@informix.com Englewood CO 80111
office: 303-850-0210
direct: 303-740-5611 Opinions expressed are mine,
fax: 303-779-4025 and do not necessarily
reflect those of my employer.
http://www.informix.com
Mickey Mestel <mickm@ix.netcom.com> wrote in message
news:38D10656.99821500@ix.netcom.com...
> 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.
> -----------------------------------------------------------------------