CODEWITHSUNDEEP
sqlsql-server-2008
quant
quant

Reputation: 23062

How to SELECT INTO with a check on each row before insertion

I want to create a table using SELECT INTO but want to avoid creating rows where two of the columns are equal but in different order to two other columns. For example, say I have a query that generates the following data

+----+----+
|col1|col2|
+----+----+
|A   |XYZ |
+----+----+
|2   |4   |
+----+----+
|WA  |AB  |
+----+----+
|W   |B   |
+----+----+
|XYZ |A   |
+----+----+

In this case I would want to add all rows except for the last, because it is a flipped duplicate of the first row. Here is a conceptual version of what I've got at the moment:

SELECT
    a.col1 AS col1,
    b.col1 AS col2,
INTO newTable
FROM a
INNER JOIN b
    ON -- some match criteria
-- If I were to end the query here, it would generate the table shown above

-- my attempt at avoiding cross duplicates:
WHERE NOT EXISTS ( 
    SELECT x.col1,x.col2
    FROM -- what name do I use here? AS x
    WHERE (x.col1 = col2 AND x.col2 = col1)
)

Note that the criteria for generating the code before that outer WHERE block is quite large, and I would prefer not to have to repeat it within the NOT EXISTS block. How do I approach this problem?

Upvotes: 1

Views: 66

Answers (1)

Anon
Anon

Reputation: 10908

SELECT DISTINCT
  CASE WHEN col2 > col1 THEN col1 ELSE col2 END,
  CASE WHEN col2 > col1 THEN col2 ELSE col1 END
FROM newTable

Upvotes: 2

Related Questions