Remove Dup records that are in reverse order

[bakershawnm]

I have a good one here.

I have a view that gives me the following information:

Item, srccount, SrcFrmRef, SrcToRef, SrcTerm, DestFrmRef, DestToRef, Destcount.

The srccount is the count of wires going from SrcFrmRef to SrcToRef and the Destcount is the same on the other end. The SrcToRef and the DestFrmRef are the same part.

So sample data would look something like this:
ITEM A 2 Ref1 Ref2 VL Ref2 Ref3 1

The problem I am getting is that I also get the following data:
ITEM A 1 Ref3 Ref2 VL Ref2 Ref1 2

I have a source view that counts the wires on the source side and provides Ref1 and Ref2. I have a similar view for the destination that does the same thing providing Ref2 and Ref3. However there is nothing in the data that designates where the start actually is so both results are correct and valid information.

Is there a way to eliminate the reverse order data without doing a bunch of programming?

 

[simian336]

Could you get an example of the result you are looking for.

Simi

 

[bakershawnm]

that was provided in this:

ITEM A | 2 | Ref1 | Ref2 | VL | Ref2 | Ref3 | 1 |
ITEM A | 1 | Ref3 | Ref2 | VL | Ref2 | Ref1 | 2 |

I want to eliminate the second line the is a mirror of the first line.

 

[PWise]

I dont really understand your data but a quick fix would be

Select Item, srccount, SrcFrmRef, SrcToRef, SrcTerm, DestFrmRef, DestToRef, Destcount
From table
union
Select Item ,Destcount ,DestToRef,DestFrmRef,SrcTerm,SrcFrmRef, SrcToRef,srccount
From Table

 

[markros]

SQL Server 2005 and up:
---------------

;with cte_All as (select myFields, row_number() over (partition by Item, SrcFrmRef, SrcToRef, SrcTerm, DestFrmRef, DestToRef order by DestCount, SrcCount DESC) as RowNum from myTable)

select * from cte_All where RowNum = 1

PluralSight Learning Library

 

[SkipVought]

I want to eliminate the second line the is a mirror of the first line.

Could you alternatively eliminate the first line that is the mirror of the second line?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}