Please try to understand that sometimes answers / help to your problems / questions / point of clarifications, may not be answered verbatimly by people since most of us are busy. One thing I' ve learn from this mailing lists is that oftentimes, answers are in the form of ideas not your direct instruction manual on how to do things step by step. No pun intended just sharing from what to expect. Have a nice day!
Peace to all! On 8/10/07, Sherwin Daganato <[EMAIL PROTECTED]> wrote: > > 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 > plug@lists.linux.org.ph (#PLUG @ irc.free.net.ph) > Read the Guidelines: http://linux.org.ph/lists > Searchable Archives: http://archives.free.net.ph > -- Destiny waits for No one. Silence.... silence... my faithful friend!
_________________________________________________ Philippine Linux Users' Group (PLUG) Mailing List plug@lists.linux.org.ph (#PLUG @ irc.free.net.ph) Read the Guidelines: http://linux.org.ph/lists Searchable Archives: http://archives.free.net.ph
