Duplicates 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

SQL Server 2000 Forums

Transact-SQL (2000)

Duplicates values?

Author

Topic

zefiros
Starting Member

16 Posts

Posted - 2008-04-14 : 07:20:27

Hi, I want to be able to find dublicates values inside a table, what i have so far is the following which works

SELECT * FROM HLP_MTRL
WHERE MT_CODE IN
( SELECT MT_CODE FROM HLP_MTRL
WHERE MT_CODE IS NOT NULL
GROUP BY MT_CODE HAVING (COUNT(MT_CODE ) > 1))
ORDER BY MT_CODE

What I want to do is use the above code to do the following
For a specific column for its distinct value to check if MT_CODE is unique. For instance

ID | ColumnA | MT_CODE
I1 | A | 1
I2 | A | 1
I3 | A | 2
I4 | B | 1
I5 | B | 2
I6 | B | 3

IDs 'I1' and 'I2' will be classified as duplicates
IDs 'I1' and 'I4' are not duplicates since their ColumnA value is different.

Many thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 07:30:37

SELECT ColumnA, MT_CODE
FROM HLP_MTRL
GROUP BY ColumnA, MT_CODE
HAVING COUNT(*) > 1

E 12°55'05.25"
N 56°04'39.16"

zefiros
Starting Member

16 Posts

Posted - 2008-04-14 : 07:42:23

zefiros
Starting Member

16 Posts

Posted - 2008-04-14 : 07:43:55

The easy way would be each time to specify a where clause equivalent to the columns attributes but then i would have to run the query for all distinct values of ColumnA

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-14 : 07:48:48

Enclose Peter's query in derived table and join it with your table.

SELECT t1.ID, t1.ColumnA, t1.MT_CODE FROM HLP_MTRL t1
JOIN
(SELECT ColumnA, MT_CODE
FROM HLP_MTRL
GROUP BY ColumnA, MT_CODE
HAVING COUNT(*) > 1
) t2
on t1.ColumnA = t2.ColumnA and t1.MT_CODE = t2.MT_CODE

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-14 : 08:12:35

Or...

select * from HLP_MTRL a where exists
    (select * from HLP_MTRL where id <> a.id and ColumnA = a.ColumnA and MT_CODE = a.MT_CODE)

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

zefiros
Starting Member

16 Posts

Posted - 2008-04-14 : 08:26:46

Thanks! It's working now

zefiros
Starting Member

16 Posts

Posted - 2008-04-15 : 05:54:58

If I want to add another column to check that is also unique for the same entry will i have to do something like this?

SELECT HLP_MTRL.ID, HLP_MTRL.MTGRPS, HLP_MTRL.PARTNO, HLP_MTRL.MT_DESCR, HLP_MTRL.MT_CODE, HLP_MTRL.ITMNO,
HLP_MTRL.MT_UNIT
FROM HLP_MTRL INNER JOIN
(SELECT MTGRPS, PARTNO
FROM HLP_MTRL AS HLP_MTRL_1
GROUP BY MTGRPS, PARTNO
HAVING (COUNT(*) > 1)) AS derivedtbl_1 ON HLP_MTRL.MTGRPS = derivedtbl_1.MTGRPS AND HLP_MTRL.PARTNO = derivedtbl_1.PARTNO
INNER JOIN
(SELECT MTGRPS, MT_DESCR
FROM HLP_MTRL AS HLP_MTRL_2
GROUP BY MTGRPS, MT_DESCR
HAVING (COUNT(*) > 1)) AS derivedtbl_2 ON HLP_MTRL.MTGRPS = derivedtbl_2.MTGRPS AND HLP_MTRL.MT_DESCR = derivedtbl_2.MT_DESCR

ORDER BY MTGRPS, PARTNO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-15 : 06:02:44

quote:
Originally posted by zefiros

If I want to add another column to check that is also unique for the same entry will i have to do something like this?

SELECT HLP_MTRL.ID, HLP_MTRL.MTGRPS, HLP_MTRL.PARTNO, HLP_MTRL.MT_DESCR, HLP_MTRL.MT_CODE, HLP_MTRL.ITMNO,
HLP_MTRL.MT_UNIT
FROM HLP_MTRL INNER JOIN
(SELECT MTGRPS, PARTNO, MT_DESCR
FROM HLP_MTRL AS HLP_MTRL_1
GROUP BY MTGRPS, PARTNO, MT_DESCR
HAVING (COUNT(*) > 1)) AS derivedtbl_1 ON HLP_MTRL.MTGRPS = derivedtbl_1.MTGRPS AND HLP_MTRL.PARTNO = derivedtbl_1.PARTNO AND HLP_MTRL.MT_DESCR = derivedtbl_1.MT_DESCR
ORDER BY MTGRPS, PARTNO,MT_DESCR

I think this will do if you want to check unique combination of three fields

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-15 : 06:11:52

quote:
Originally posted by zefiros

If I want to add another column to check that is also unique for the same entry will i have to do something like this?

Why did you not post this originally?
Why do you keep releasing a little more bit of information for each post?

This is not fun. We do help on our spare time, for free.
What gives you the right to occupy time that could be better spent on other people with problems/questions?
Now you have made a lot of people work twice with same problem...

E 12°55'05.25"
N 56°04'39.16"

zefiros
Starting Member

16 Posts

Posted - 2008-04-15 : 06:13:39

Mm not exactly , since
ID | ColumnA | MT_CODE | MT_DESCR
I1 | A | 1 | 1
I2 | A | 1 | 2
I3 | A | 2 | 3
I4 | B | 1 | 1
I5 | B | 2 | 2
I6 | B | 3 | 1

I1, I2 THAT BELONG TO 'A' ARE DUPLICATES BECAUSE THEIR CODE IS BOTH SET TO 1
I4, I5 THAT BELONG TO 'B' ARE DUPLICATE BECAUSE THEIR DESCRIPTION IS BOTH SET TO 1 ALTHOUGH THEIR CODE IS UNIQUE

As a result i should expect somethin like this

ID | ColumnA | MT_CODE | MT_DESCR
I1 | A | 1 | 1
I2 | A | 1 | 2
I4 | B | 1 | 1
I6 | B | 3 | 1

zefiros
Starting Member

16 Posts

Posted - 2008-04-15 : 06:14:22

i know its not for fun, if i knew this from yesterday i would have post it!

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-15 : 06:19:26

[code]select * from HLP_MTRL a where exists
(select * from HLP_MTRL where id <> a.id and ColumnA = a.ColumnA
and (MT_CODE = a.MT_CODE or MT_DESCR = a.MT_DESCR))[/code]

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-15 : 06:21:49

[code]-- Prepare sample data
DECLARE @Sample TABLE (ID CHAR(2), ColumnA CHAR(1), MT_CODE INT, MT_DESCR INT)

INSERT @Sample
SELECT 'I1', 'A', 1, 1 UNION ALL
SELECT 'I2', 'A', 1, 2 UNION ALL
SELECT 'I3', 'A', 2, 3 UNION ALL
SELECT 'I4', 'B', 1, 1 UNION ALL
SELECT 'I5', 'B', 2, 2 UNION ALL
SELECT 'I6', 'B', 3, 1

-- Show the expected output
SELECT s.ID,
s.ColumnA,
s.MT_CODE,
s.MT_DESCR
FROM @Sample AS s
LEFT JOIN (
SELECT ColumnA,
MT_CODE
FROM @Sample
GROUP BY ColumnA,
MT_CODE
HAVING COUNT(*) > 1
) AS a ON a.ColumnA = s.ColumnA
AND a.MT_CODE = s.MT_CODE
LEFT JOIN (
SELECT ColumnA,
MT_DESCR
FROM @Sample
GROUP BY ColumnA,
MT_DESCR
HAVING COUNT(*) > 1
) AS b ON b.ColumnA = s.ColumnA
AND b.MT_DESCR = s.MT_DESCR
WHERE a.ColumnA IS NOT NULL
OR b.ColumnA IS NOT NULL[/code]

E 12°55'05.25"
N 56°04'39.16"

zefiros
Starting Member

16 Posts

Posted - 2008-04-15 : 06:29:47

Many thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources