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.
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 informationtbl_refnum carrier pronum1021970 UPGF 6788744201021970 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.JimEveryday 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 table1where tbl_refnum = '1021970'It is returning this:tbl_refnum carrier pronum1021970 UPGF 6788744201021970 CNWY 717916220But it should only return tbl_refnum carrier pronum1021970 UPGF 678874420ORtbl_refnum carrier pronum1021970 CNWY 717916220A 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 youSELECT tbl_refnumFROM table1GROUP BY tbl_refnumHAVING COUNT(DISTINCT pronum) > 1 JimEveryday 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. |
|
|
|
|
|