Re: Select duplicated values

On Nov 21, 2011, at 21:11, David Johnston <polobo(at)yahoo(dot)com> wrote:

> On Nov 21, 2011, at 17:23, jeffrey <johjeffrey(at)hotmail(dot)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.