Getting Duplicate Rows - 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 2005 Forums

Transact-SQL (2005)

Getting Duplicate Rows

Author

Topic

besadmin
Posting Yak Master

116 Posts

Posted - 2010-10-13 : 14:08:52

I just want the Row that has a UPC and UPC with Check digit. IF there is one WITHOUT Check Digit and one WITH I only want the one WITH. Can anyone help me with the please? Thanks a ton!!

There are 13 duplicates of this issue, here is an example of 2 rows where i only want the one WITH the check digit


33RMC 72	270335	9781	26250	chain 20in rmc 3/8p .050g	11.8	23.6	STK	Other	N	79571138113	795711381134
33RMC 72	270335	9781	26250	chain 20in rmc 3/8p .050g	11.8	23.6	STK	Other	N	79571138113	NULL


	Select DISTINCT
			A.Item_ID,
			A.Price_Page_UID,
			A.Customer_ID,
			A.inv_mast_UID,
			A.item_desc,
			A.Price,
			A.List_Price,
			A.class_id1,
			A.Pcat,
			A.Obsolete,
			A.UPC_Code,
			(cast(C.upc_code as varchar) + cast(C.check_digit as varchar)) as [With Check digit] --Added Line *****************************
		from commerce_Center_Support..v_Item_Price_by_Customer A
		left Join 
			(
				Select Max(price_page_UID) as Price_Page_UID
				from commerce_Center_Support..v_Item_Price_by_Customer
				where Customer_ID = '9781'
				Group by inv_mast_UID
		) B 
		on A.price_Page_UID = B.Price_Page_UID
			and A.Customer_ID = '9781'
		LEFT JOIN Commerce_Center..p21_view_inventory_supplier C --Added Line *****************************
		ON A.Item_ID = C.Item_ID --Added Line *****************************
		Where B.price_page_uid is not null
			and A.class_id5 <> ''
ORDER BY A.Item_ID

besadmin
Posting Yak Master

116 Posts

Posted - 2010-10-13 : 14:50:51

sorry there were no column headers. the last column is the one causing my issue. somehow it is getting one with NULL and one with a upc WITH CHECK DIGIT??
can anyone please help me?
any ideas are greatly appreciate as always my friends.
Thanks a TON!

besadmin
Posting Yak Master

116 Posts

Posted - 2010-10-13 : 15:45:10

ok, how about this. isnt there a CASE or something i can do where if the rows are the same except for check digit, give me the one that is NOT NULL


ok, how about this. isnt there a CASE or something i can do where if the rows are the same except for check digit, give me the one that is NOT NULL

[code]
Item ID         UPC             UPCWithCheck
7010 881 3100	79571136777	NULL
7010 881 3100	79571136777	795711367770

[\code]

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-13 : 15:56:17

is the upc always 11 digits?

besadmin
Posting Yak Master

116 Posts

Posted - 2010-10-13 : 16:00:39

yes

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-13 : 16:05:44

wait, i misunderstood...if upcWithCheckDigit is in its' own column, just select records WHERE upcWithCheckDigit IS NOT NULL.

If check digit is included in (appended to) the UPC then select WHERE Len(upc) = 12

besadmin
Posting Yak Master

116 Posts

Posted - 2010-10-13 : 16:15:13

Yea, sorry if it is confusing. All I want to do is add the column UPCWithCheck Digit. I get this from the join of another table. If the CheckDigit is NULL thats ok, If it is something, thats ok. The problem is when there is NULL and SOMETHING, then I get a ROW for each. Here is an example of that from the joining table


ItemID   UPC Code     CheckDigit
33RMC    79571138113  4
33RMC    79571138113  NULL

I need something like if NULL and NOT NULL Exisit for SameID use NOT NULL Otherwise use whatever??

Thanks a ton for the replys. and any other help friends!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-13 : 16:32:18

Why not just add

And C.check_digit is not null

to the WHERE clause?

besadmin
Posting Yak Master

116 Posts

Posted - 2010-10-13 : 16:36:26

yes, i thought of that, but it doenst work for what we need. Many items have no UPC or check digit. So, IS NOT NULL takes all those out. Sadly it is only 13 out of 23023 that are giving me this problem. I need to get it figured out though. any more ideas are greatly welcome! thanks again though russell!

TimSman
Posting Yak Master

127 Posts

Posted - 2010-10-13 : 17:09:45

What about GROUP BY on the ItemID?

besadmin
Posting Yak Master

116 Posts

Posted - 2010-10-13 : 17:16:21

gave it a try, didnt work either. Thanks a lot though!
I am looking at a CASE might work.
CASE UPCWithCheckDigit
UPC is null then UPCWIthCheckDigit = NULL
UPC is not null then UPC = select * from table when UPC is not null

Something like that? i am not sure how to set it up though? any ideas? i am working with the original query posted above
thanks a lot for any help!

TimSman
Posting Yak Master

127 Posts

Posted - 2010-10-13 : 17:18:53

Take out the DISTINCT.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-13 : 17:24:30

[code]Select DISTINCT
A.Item_ID,
A.Price_Page_UID,
A.Customer_ID,
A.inv_mast_UID,
A.item_desc,
A.Price,
A.List_Price,
A.class_id1,
A.Pcat,
A.Obsolete,
A.UPC_Code,
(cast(C.upc_code as varchar) + coalesce(cast(C.check_digit as varchar),'')) as [With Check digit]
from commerce_Center_Support..v_Item_Price_by_Customer A

left Join
(
Select Max(price_page_UID) as Price_Page_UID
from commerce_Center_Support..v_Item_Price_by_Customer
where Customer_ID = '9781'
Group by inv_mast_UID
) B
on A.price_Page_UID = B.Price_Page_UID and A.Customer_ID = '9781'

LEFT JOIN
(
select row_number() over (partition by Item_Id order by check_digit desc) as rownum,
Item_Id,
upc_code,
check_digit
from Commerce_Center..p21_view_inventory_supplier
) C
ON A.Item_ID = C.Item_ID and C.rownum=1

Where B.price_page_uid is not null
and A.class_id5 <> ''
ORDER BY A.Item_ID [/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

besadmin
Posting Yak Master

116 Posts

Posted - 2010-10-13 : 17:34:12

YES! YES! YES! webfred!
You get all the points!! Perfect! This was definitley a solution beyond my SQL skills.

Thanks a ton though to EVERYONE who replied! It is soo GREATLY appreciated!!

Thanks again to webfred for finding the solution that ended up working for me!!

Thanks again friends!

LAter!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-13 : 17:40:09

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources