How to select unique rows (comparing by a few columns)?-CodePudding

I want to select unique rows from a table (without repeating the combination of 'f' and 'x' fields).

The table:

| f | x | z |
|---|—--|---|
| 1 | 1 | a |
| 1 | 2 | b |
| 1 | 3 | c |
| 1 | 3 | d |

The result:

| f | x | z |
|---|—--|---|
| 1 | 1 | a |
| 1 | 2 | b |

CodePudding user response：

WITH
  check_repetitions AS
(
  SELECT
    *,
    COUNT(*) OVER (PARTITION BY f, x)   AS repetitions
  FROM
    your_table
)
SELECT
  f, x, z
FROM
  check_repetitions
WHERE
  repetitions = 1

CodePudding user response：

The following query groups rows in "the_table" by "f" and "x", selects the minimum value of "z" in each group and filters out groups with a count greater than 1, returning only unique combinations of "f" and "x".

SELECT f, x, MIN(z) AS z
FROM the_table
GROUP BY f, x
HAVING COUNT(*) = 1;