How to get uncommon data from 2 sql queries

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)

How to get uncommon data from 2 sql queries

Author

Topic

skybvi
Posting Yak Master

193 Posts

Posted - 2012-01-13 : 08:24:51

Hi,
My query
select ITEMNMBR,ITEMUPCCODE from ep40101
yields 17140 rows

My query
select DISTINCT ITEMNMBR,ITEMUPCCODE from ep40101
yields 16960 rows...

How to get those (17140-16960 =180 rows)

I want to see those 180 rows of data.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-13 : 08:35:01

You can use the EXCEPT clause like this:

(
	select ITEMNMBR,ITEMUPCCODE from ep40101
	EXCEPT 
	select DISTINCT ITEMNMBR,ITEMUPCCODE from ep40101
)
UNION ALL
(
	select DISTINCT ITEMNMBR,ITEMUPCCODE from ep40101
	EXCEPT 
	select ITEMNMBR,ITEMUPCCODE from ep40101
)

An alternative would be to use the NOT EXISTS construct in a similar manner.

skybvi
Posting Yak Master

193 Posts

Posted - 2012-01-13 : 08:36:44

i JUST TRIED AND I GETTING
0 ROWS :(

Regards,
Sushant
DBA
Virgin Islands(U.K)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-13 : 08:39:06

My bad, I thought they were two tables - they are the same table! Sorry about that.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-13 : 08:41:18

Try this?

;WITH cte AS 
(
	SELECT ITEMNMBR,ITEMUPCCODE,
		ROW_NUMBER() OVER (PARTITION BY ITEMNMBR,ITEMUPCCODE ORDER BY (SELECT NULL)) AS RN
	from ep40101
)
SELECT ITEMNMBR,ITEMUPCCODE FROM CTE WHERE RN > 1;

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-13 : 08:48:16

Or:

SELECT
	ITEMNMBR,ITEMUPCCODE
FROM
	ep40101 e1
WHERE EXISTS
( 
  SELECT ITEMNMBR,ITEMUPCCODE FROM ep40101 e2
  WHERE e1.ITEMNMBR = e2.ITEMNMBR AND e1.ITEMUPCCODE = e2.ITEMUPCCODE
  GROUP BY e2.ITEMNMBR,e2.ITEMUPCCODE
  HAVING COUNT(*) > 1
)

You don't have to use this method, I posted it "just because", and just to keep one step ahead of Brett Kaiser aka X002548

In any case, this may be more efficient if you have a large number of rows.

skybvi
Posting Yak Master

193 Posts

Posted - 2012-01-13 : 08:49:11

WOW SUNITABECK
tHAT WAS IT.

thanks a lot

Regards,
Sushant
DBA
Virgin Islands(U.K)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources