On Nov 21, 2011, at 21:11, David Johnston <pol...@yahoo.com> wrote: > On Nov 21, 2011, at 17:23, jeffrey <johjeff...@hotmail.com> wrote: > >> Lets say that the primary key column is A. I am trying to select all >> the rows with duplicated values in columns B, C, and D. >> >> I am not too experienced in SQL syntax, and I've used the following: >> select A from table_name where B+C+D in (select B+C+D from table_name >> group by B+C+D having count(*)>1 ) >> >> I'm looking for a better way, since I am just adding the three columns >> together right now. >> >> Jeffrey >> > > Do you really want these to evaluate to equal (B, C, D)? > > (1, 0, 1) = (0, 1, 1); they both sum to 2 but both B and C are unequal > > There is no need to do anything other than list each field individually in > both the SELECT and the GROUP BY. > > SELECT B, C, D > FROM table > GROUP BY B, C, D > HAVING count(*) > 1; > > Whatever possessed you to consider that you had to add them to get what you > need? (assuming you don't want my examples to be considered equal) Besides > the fact it will give you WRONG RESULTS any decent set of GROUP BY examples > will show you that you can list/use multiple fields in a grouping query. > > Now, if you truly want the two samples above to evaluate to equal then you do > need to do some form of consolidation (like the adding in your example). > That, however, would be very unusual. > > David J. > >
So, in a sub-select you would do: ... WHERE ROW(B, C, D) IN (SELECT B, C, D FROM ... GROUP BY B, C, D) Note that the word ROW is optional ( but not the parentheses ) David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
