Duplicate values... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Duplicate values...

Author

Topic

CF
Starting Member

4 Posts

Posted - 2007-02-08 : 11:54:13

I'm ok at SQL, but this has really confused me - please help!

I've got a table which (amongst others) has two columns - myID, and barcode. Ideally myID and barcode should be a 1 to 1 relationship... but it's not. So how can I get a list of all rows where 1 barcode value has >1 myID? (and also vice versa if possible). Thanks!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-02-08 : 13:02:20

select barcode, count(myID)
from myTable
group by barcode
having count(myID) > 1

vice versa is a matter of changing the column names.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

CF
Starting Member

4 Posts

Posted - 2007-02-08 : 13:16:34

Thanks for the very prompt reply! Unfortunately I wasn't very clear in my specs... this query shows the number of myIDs for each barcode, but I want to show the barcodes which have multiple myIDs. If a barcode has one myID then that's ok.


myID  barcode
1     A
2     B
3     A
3     C
2     B
2     B

Here, I want to know that barcode A has got myID 1 and 3. B and C are fine so shouldn't be displayed. I basically want to look at each case (probably a few hundred) and manually assign a 'myID' for all rows with a particular barcode.

Apologies if I'm making this more complicated than necessary...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-02-08 : 13:20:23

select *
from myTable join
(
select barcode
from myTable
group by barcode
having count(myID) > 1
) t2 on t1.barcode = t2.barcode

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

CF
Starting Member

4 Posts

Posted - 2007-02-21 : 09:03:16

I managed to get it to do what I wanted with:

select barcode, count(distinct myid) as 'Number of different myids'
from mytable
group by barcode having count(distinct myid) > 1
order by count(distinct myid) desc

Thanks! :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-21 : 09:57:36

More efficient.

SELECT   Barcode
FROM     MyTable
GROUP BY Barcode
HAVING   MIN(MyID) <> MAX(MyID)

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources