CODEWITHSUNDEEP
sql
nik0x1
nik0x1

Reputation: 1345

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

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 |

Upvotes: 1

Views: 107

Answers (3)

Dosmax
Dosmax

Reputation: 17

You can use the following query to select only rows where the combination of columns f and x do not repeat:

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

This query will group the rows based on the values of f and x, and then return only the rows where the combination of f and x occurs only once. The function MIN is used to select a single value for z for each group.

Upvotes: 1

Andreas Violaris
Andreas Violaris

Reputation: 4158

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;

Upvotes: 7

MatBailie
MatBailie

Reputation: 86716

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

Upvotes: 2

Related Questions