Value Not Distinct - 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

Value Not Distinct

Author

Topic

warrend
Starting Member

15 Posts

Posted - 2013-02-22 : 09:58:43

I am using SQL 2005.

I have a table that gets populated with EDI information, so information should be unique to each LTL pro/tracking number. I found they are not. So I need to find the records with non-unique information. I'm completely stuck on how to get the results I want.

example table information
tbl_refnum carrier pronum
1021970 UPGF 678874420
1021970 CNWY 717916220

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-22 : 10:05:58

For the example you posted, what makes it non-unique? Is it that the tbl_refnum is the same, butcarrier is different, or that pronum is different? Or is this a case where the data is unique because carrier is different? Not sure if this will work for you, but you might try this.

SELECT LTL FROM TheTable GROUP BY LTL HAVING COUNT(*) > 1

If that does not give you what you want, post some sample data that includes unique records and non-unique records and the expected output you want to see.

warrend
Starting Member

15 Posts

Posted - 2013-02-22 : 10:26:10

The tbl_refnum will be unique and the carrier EDI information will be unique. So in the example I gave, these records have the same tbl_refnum even though they should not because they are two different EDI shipments.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-02-22 : 10:38:47

Please post some sample data that demonstrates what you are having trouble with. I can't tell from your posts even how many tables you're dealing with.

Jim

Everyday I learn something that somebody else already knew

warrend
Starting Member

15 Posts

Posted - 2013-02-22 : 10:46:52

It's one table.

So for this select example:

select *
from table1
where tbl_refnum = '1021970'

It is returning this:
tbl_refnum carrier pronum
1021970 UPGF 678874420
1021970 CNWY 717916220

But it should only return
tbl_refnum carrier pronum
1021970 UPGF 678874420
OR
tbl_refnum carrier pronum
1021970 CNWY 717916220
A unique record.

I need to find all those that have more than one pronum but the same tbl_refnum in the table.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-02-22 : 10:53:51

The James' query with a tweak should do it fot you
SELECT tbl_refnum
FROM table1
GROUP BY tbl_refnum
HAVING COUNT(DISTINCT pronum) > 1

Jim

Everyday I learn something that somebody else already knew

warrend
Starting Member

15 Posts

Posted - 2013-02-22 : 11:18:13

I believe that did it! Thanks so much everyone.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources