Distinct pair of values SQL

Fetching distinct pairs

If you want to efficiently retrieve pair combinations, SQL provides two nifty keywords: DISTINCT and GROUP BY.

Just want distinct pairs? Keep it simple:

-- Sometimes, less is more. And remember: SQL is like a teen, it hates redundancy.
SELECT DISTINCT a, b FROM pairs;


In a complex query scenario? GROUP them:

-- When your data is a messy room, GROUP BY works like a neat-freak roommate.
SELECT a, b FROM pairs GROUP BY a, b;


Here, the GROUP BY clause ensures aggregation only where necessary and successfully dodges the infamous "not a group by expression" error. But be careful. Overuse of grouping might lead to performance hits.

How to count the unique pairs?

When you want to play a numbers game and your task is to count unique pairs, here you go:

-- How many unique pairs walked into the database?
SELECT COUNT(*) FROM (SELECT DISTINCT a, b FROM pairs) AS subquery;


Remember COUNT(DISTINCT a, b) won’t work in all SQL databases because by syntax it's a party pooper. In such cases, subqueries or temporary tables save the day!

Symmetrical pair handling

If you consider (1,2) and (2,1) to be twins, use a CASE expression:

-- It's all about perspective, isn't it? 
SELECT DISTINCT 
    CASE WHEN a < b THEN a ELSE b END AS small,
    CASE WHEN a < b THEN b ELSE a END AS big
FROM pairs;


This surely does justice to symmetrical pairs by not identifying them as separate entities.

SQL flavors and handling them

Like ice-cream, SQL also comes in different flavors - MS-SQL, MySQL, or Postgres. Every flavor has its own charm:

• MS-SQL’s sweet spot? Use FULL OUTER JOIN for complex pair needs.
• In MySQL, you can substitute GROUP_CONCAT for DISTINCT while dealing with string concatenation. Taste varies after all!
• Postgres offers some tasty treats — powerful array functions and DISTINCT ON.

Smart query performance tips

Boost query performance with indices on columns involved in DISTINCT or GROUP BY. Make EXPLAIN plans your best friends to understand what's happening under the hood. And as always, KISS (Keep It Simple, SQL) - simpler queries, happier databases!