Distinct Pair of Values SQL

Distinct pair of values SQL

What you mean is either

SELECT DISTINCT a, b FROM pairs;

or

SELECT a, b FROM pairs GROUP BY a, b;

Select Distinct pair of column values

An ANSI compliant way of doing this would be to rearrange each pair of A1 and A2 values as min/max using CASE expressions. Then just select distinct on this derived table.

SELECT DISTINCT
    A1, A2
FROM
(
    SELECT
        CASE WHEN A1 < A2 THEN A1 ELSE A2 END AS A1,
        CASE WHEN A1 < A2 THEN A2 ELSE A1 END AS A2
    FROM yourTable
) t

distinct pairs plus the number of times each pair appears

select 
    a,b,count(*) c
from 
    pairs
group by 
    a,b

How to write SQL query that selects distinct pair values for specific criteria?

It is relatively simple using the stored function:

--drop function if exists f();
--drop table if exists t;
create table t(x text,y text, z int);
insert into t values
  ('t1','p1',65),
  ('t1','p2',60),
  ('t1','p3',20),
  ('t2','p1',60),
  ('t2','p2',59),
  ('t2','p3',15)/*,
  ('t3','p1',20),
  ('t3','p2',60),
  ('t3','p3',40)*/;

create function f() returns setof t immutable language plpgsql as $$
declare
  ax text[];
  ay text[];
  r t;
begin
  ax := '{}'; ay := '{}';
  loop
    select * into r
      from t
      where x <> all(ax) and y <> all(ay)
      order by z desc, x, y limit 1;
    exit when not found;
    ax := ax || r.x; ay := ay || r.y;
    return next r;
  end loop;
end $$;

select * from f();
╔════╤════╤════╗
║ x  │ y  │ z  ║
╠════╪════╪════╣
║ t1 │ p1 │ 65 ║
║ t2 │ p2 │ 59 ║
╚════╧════╧════╝

However if uncomment the third bunch of the values the result will be different:

╔════╤════╤════╗
║ x  │ y  │ z  ║
╠════╪════╪════╣
║ t1 │ p1 │ 65 ║
║ t3 │ p2 │ 60 ║
║ t2 │ p3 │ 15 ║
╚════╧════╧════╝

Upd: and the equivalent using recursive CTE on the same test data:

with recursive r as (
  (select x, y, z, array[x] as ax, array[y] as ay from t order by z desc, x, y limit 1)
  union all
  (select t.x, t.y, t.z, r.ax || t.x, r.ay || t.y from t, r 
  where not (t.x = any(r.ax) or t.y = any(r.ay)) 
  order by t.z desc, t.x, t.y limit 1))
select * from r;

selecting distinct pairs of values in SQL

Your question seems to imply two things:

1. When listing source/destination pairs you only want to see the pairs in one direction, e.g., (A,B) but not (B,A).

2. The list should omit pairs where the source and destnation are the same, e.g., (D,D)

In that case the query...

SELECT DISTINCT source, destination
FROM
    (
            SELECT source, destination
            FROM SomeTable
        UNION ALL
            SELECT destination, source
            FROM SomeTable
    )
WHERE source < destination

...when run against [SomeTable] containing...

source  destination
------  -----------
A       B          
B       A          
A       B          
C       D          
D       D          
E       D          

...will produce:

source  destination
------  -----------
A       B          
C       D          
D       E          

SQL DISTINCT Pair of Values in Elasticsearch

You can use multi_terms aggregation

{
  "size": 0,
  "aggs": {
    "unique_pair": {
      "multi_terms": {
        "terms": [
          {
            "field": "username"
          },
          {
            "field": "city"
          }
        ]
      }
    }
  }
}

How can I count unique pairs of values in SQL?

You've almost got it correct... You just add an additional GROUP BY column like so:

SELECT [first_name], [last_name], COUNT(*) AS [Count] 
FROM [Table]
GROUP BY [first_name], [last_name]
ORDER BY [Count] DESC;

---

Related Topics