CODEWITHSUNDEEP
sqlsql-servert-sql
umbersar
umbersar

Reputation: 1931

Filtering result of Cross Join to select distinct column combination

I have Cross Joined 3 tables and now i want to filter down the result using combined column values. What i mean is that if a row has values (a,b,c) for 3 columns, then it be treated as same as a row with these 3 values(b,c,a) or any other combination of a, b and c.

The query that generated the results is:

;WITH CTE
     AS (SELECT T1.LastName                                       AS T1LASTNAME,
                T2.LastName                                       AS T2LASTNAME,
                T3.LastName                                       AS T3LASTNAME,
                ( T1.TOTALSALES + T2.TOTALSALES + T3.TOTALSALES ) AS TOTALSALES,
                ( T1.SALARY + T2.SALARY + T3.SALARY )             AS TOTALSALARY
         FROM   #TOPSALESPERSON AS T1
                CROSS JOIN #TOPSALESPERSON AS T2
                CROSS JOIN #TOPSALESPERSON AS T3
         WHERE  T1.BusinessEntityID != T2.BusinessEntityID
                AND T2.BusinessEntityID != T3.BusinessEntityID
                AND T1.BusinessEntityID != T3.BusinessEntityID
                AND T1.NAME != T2.NAME
                AND T1.NAME != T3.NAME
                AND T3.NAME != T2.NAME)
SELECT DISTINCT *
FROM   CTE
WHERE  CTE.TOTALSALARY < 210000
ORDER  BY TOTALSALES DESC

So here, these 3 rows should be filtered down to one row:

T1LASTNAME  T2LASTNAME  T3LASTNAME   TOTALSALES  TOTALSALARY
------------------------------------------------------------
Campbell    Mitchell    Saraiva      2924710.74  195000.00
Campbell    Saraiva     Mitchell     2924710.74  195000.00
Mitchell    Campbell    Saraiva      2924710.74  195000.00

Here is a SQL fiddle link for the schema and data to play with: http://sqlfiddle.com/#!9/c456d8/1/0

Thanks

Upvotes: 1

Views: 1664

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522396

Change the restriction on the names to the following logic:

T1.NAME <= T2.NAME AND
T1.NAME <= T3.NAME AND
T2.NAME <= T3.NAME

This solves your original problem because now the three names can only appear in ascending order.

;WITH CTE
 AS (SELECT T1.LastName                                       AS T1LASTNAME,
            T2.LastName                                       AS T2LASTNAME,
            T3.LastName                                       AS T3LASTNAME,
            ( T1.TOTALSALES + T2.TOTALSALES + T3.TOTALSALES ) AS TOTALSALES,
            ( T1.SALARY + T2.SALARY + T3.SALARY )             AS TOTALSALARY
     FROM   #TOPSALESPERSON AS T1
            CROSS JOIN #TOPSALESPERSON AS T2
            CROSS JOIN #TOPSALESPERSON AS T3
     WHERE  T1.BusinessEntityID != T2.BusinessEntityID
            AND T2.BusinessEntityID != T3.BusinessEntityID
            AND T1.BusinessEntityID != T3.BusinessEntityID
            AND T1.NAME <= T2.NAME
            AND T1.NAME <= T3.NAME
            AND T2.NAME <= T3.NAME)

Note that we could still get duplicate records here if two or three names be the same. But your SELECT DISTINCT would actually take care of that problem and remove those duplicates.

Upvotes: 4

Ramandeep Walia
Ramandeep Walia

Reputation: 1

To my knowledge this isn't a way to do this without writing a custom function. You can write a function which takes two strings as input. The first input string is the concatenation of the name columns you have mentioned in your example. The concatenation should delimit the string by the delimiter of your choice. The second input string should be your delimiter.

Here is how I concatenated the names:

concat(t1lastname, '--', t2lastname, '--', t3lastname)

Your custom function then takes the input string and tokenizes the string by delimiter and sorts it. Once the names have been sorted, they are concatenated back together and returned as output in sorted order.

function sort_delimited_string(concatenated_string, delimiter_string) 
{
    ...
    return concatenated_sorted_string;
}

your query would look somehting like this:

select distinct(sort_delmited_string(concat(t1lastname, '--', t2lastname, '--', t3lastname), '--')) from table1;

Upvotes: 0

Related Questions