CODEWITHSUNDEEP
mysqlsql
Nicola Pedretti
Nicola Pedretti

Reputation: 5166

Get all rows where the permutation of column a and b is unique

I am trying to fetch all rows from a table where the permutation of column_a and column_b is unique

column_a column_b
1         2
1         5
2         1
1         2

the query should select

column_a column_b
1         2
1         5
2         1

I have seen other posts on this matter, but they all seem to focus on both columns being unique separately, so that column_a can never present the same value twice. This is not what I am trying to do.

Even better would be to have a query that selects all rows where this permutation is not unique -- the reason I am doing this is that i then need to run some logic to decide which of the duplicate rows needs to stay and which need to be deleted.

Upvotes: 0

Views: 514

Answers (3)

Nicola Pedretti
Nicola Pedretti

Reputation: 5166

I upvoted the previous answers as they provide an answer to the main question:

fetch all rows from a table where the permutation of column_a and column_b is unique

However, I ended up using a combination of the answers provided to find all rows where the permutation of column_a and column_b is not unique. The reason is that i wanted to see all the rows that had duplicate column_a and column_b and not just one of them.

Here is the query:

SELECT column_a, column_b
FROM table 
WHERE id NOT IN (
    SELECT id
    FROM table
    GROUP BY column_a, column_b
    HAVING COUNT(*) = 1
)

Upvotes: 0

Uueerdo
Uueerdo

Reputation: 15951

This will give you the combinations that are unique:

SELECT a, b
FROM theTable
GROUP BY a, b
HAVING COUNT(*) = 1;

...and changing that = to <> will give you the non-unique ones.

If you're trying to eliminate duplicates, and have another identifying field (like an auto-incremented PK), you can just select the ones you want to keep by selecting MIN(id) or MAX(id) along with the pairing.

Upvotes: 1

The Impaler
The Impaler

Reputation: 48810

DISTINCT filters out repeated rows. Please note DISTINCT applies to the full row.

select distinct column_a, column_b from my_table

And for your second question:

...a query that selects all rows where this permutation is not unique...

select column_a, column_b
from my_table
group by column_a, column_b
having count(*) > 1

Upvotes: 2

Related Questions