I have a table with two columns with data like this:
1,2
1,3
1,4
2,1
2,2
3,1
I want to select just unique combinations, so out of those I would end up with:
1,2
1,3
1,4
2,2
because 1,2 is the same combination as 2,1 etc
How would I go about that in a SQL statement?
In reality, my table has a third column and I want to add a where clause based on that third column so that only those rows are considered
SELECT * FROM (
SELECT
CASE WHEN Col1 <= Col2 THEN Col1 ELSE Col2 END AS Col1,
CASE WHEN Col1 <= Col2 THEN Col2 ELSE Col1 END AS Col2
FROM
MyTable
) Ordered
GROUP BY
Col1, Col2
You could do it without the subquery by GROUPing on the CASE expressions, but it's longer to read.
SELECT.Another way to achieve the same thing:
SELECT a, b
FROM tableX
WHERE a <= b
AND (other conditions)
UNION
SELECT b, a
FROM tableX
WHERE a > b
AND (other conditions) ;
This variation may be different (regarding efficiency), depending on the indexes you have:
SELECT *
FROM
( SELECT a, b
FROM tableX
WHERE (other conditions)
UNION
SELECT b, a
FROM tableX
WHERE (other conditions)
) AS tmp
WHERE a <= b ;
There are two versions of Plan Explorer: FREE and PRO. The pro version has some additional features but they're not needed for basic performance comparisons.You can try something like:
select distinct col1, col2 from table
where col2 + '-' + col1 not in (select col1 + '-' + col2 from your_table)
Notice that you have to concatenate the fields and it depends of the column type (col1 + '-' + col2 works well with char and varchar types)
23,1 as the same that 2,31
where col2, col1 not in (select col1, col2 from your_table) but you can't do that in SQL ServerHow about:
SELECT
COL1, COL2, COUNT(*)
FROM
Your_Table
GROUP BY
COL1, COL2
