Multiple NOT distinct

Asked 2008-11-06T17:49:41.437

I've got an MS access database and I would need to create an SQL query that allows me to select all the not distinct entries in one column while still keeping all the values.

In this case more than ever an example is worth thousands of words:

Table:

A B C
1 x q
2 y w
3 y e
4 z r
5 z t
6 z y

SQL magic

Result:

B C
y w
y e
z r
z t
z y

Basically it removes all unique values of column B but keeps the multiple rows of the data kept. I can "group by b" and then "count>1" to get the not distinct but the result will only list one row of B not the 2 or more that I need.

Any help?

Thanks.

sql ms-access distinct

Edit Report

2 Answers

Another way of returning the results you want would be this:

select *
from
    my_table
where 
    B in 
    (select B from my_table group by B having count(*) > 1)

answered 2008-11-06T18:05:26.990

join the unique values of B you determined with group by b and count > 1 back to the original table to retrieve the C values from the table.

answered 2008-11-06T17:54:13.237

Multiple NOT distinct

2 Answers

Your Answer