On 8/7/07, Tito Mari Francis Escaño <[EMAIL PROTECTED]> wrote: > Pls provide me pointers how to determine via SQL if a PostgreSQL > record has a duplicate entry.
Depending on what you wanted to see in the result tuples, you can either do it with the HAVING clause which the other posters before me have already suggested, or use SQL JOIN which allows you to include the other attributes of the tuples. test=# SELECT * FROM t1; id | fname | lname ----+-------+------- 1 | baz | quux 2 | foo | bar 3 | foo | bar (3 rows) test=# SELECT fname, lname, COUNT(*) FROM t1 GROUP BY 1, 2 HAVING COUNT(*) > 1; fname | lname | count -------+-------+------- foo | bar | 2 (1 row) test=# SELECT a.id, a.fname, a.lname FROM t1 a, t1 b WHERE a.fname = b.fname AND a.lname = b.lname AND a.id <> b.id; id | fname | lname ----+-------+------- 2 | foo | bar 3 | foo | bar (2 rows) In postgresql, if you want to remove the extra tuples in the result of the SQL JOIN example, you can do so simply with DISTINCT ON clause: test=# SELECT DISTINCT ON (a.fname, a.lname) a.id, a.fname, a.lname FROM t1 a, t1 b WHERE a.fname = b.fname AND a.lname = b.l name AND a.id <> b.id; id | fname | lname ----+-------+------- 2 | foo | bar (1 row) _________________________________________________ Philippine Linux Users' Group (PLUG) Mailing List [email protected] (#PLUG @ irc.free.net.ph) Read the Guidelines: http://linux.org.ph/lists Searchable Archives: http://archives.free.net.ph
