Distinct on specific columns in SQL

Question

I know someone on here already asked the similar questions. However, most of them still want to return the first row or last row if multiple rows have the same attributes. For my case, I want to simply discard the rows which have the same specific attributes.

For example, I have a toy dataset like this:

gender age  name
  f     20   zoe
  f     20   natalia
  m     39   tom
  f     20   erika 
  m     37   eric
  m     37   shane
  f     22   jenn

I only want to distinct on gender and age, then discard all rows if those two attributes, which returns:

gender age  name
  m     39   tom
  f     22   jenn

Mureinik · Accepted Answer · 2018-01-10 21:42:44Z

2

You could use the window (analytic) variant of count to find the rows that have a just one occurance of the gender/age combination:

SELECT gender, age, name
FROM   (SELECT gender, age, name, COUNT(*) OVER (PARTITION BY gender, age) AS cnt
        FROM   mytable) t
WHERE  cnt = 1

answered Jan 10, 2018 at 21:42

Mureinik

316k54 gold badges401 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

digital.aaron · Accepted Answer · 2018-01-10 21:47:18Z

1

Use the HAVING clause in a CTE.

;WITH DistinctGenderAges AS
(
    SELECT gender
        ,age
    FROM YourTable
    GROUP BY gender
        ,age
    HAVING COUNT(*) = 1
)
SELECT yt.gender, yt.age, yt.name
FROM DistinctGenderAges dga
INNER JOIN YourTable yt ON dga.gender = yt.gender AND dga.age = yt.age

edited Jan 10, 2018 at 21:47

answered Jan 10, 2018 at 21:41

digital.aaron

5,7152 gold badges27 silver badges46 bronze badges

2 Comments

paparazzo Over a year ago

Name is not part of the distinct

digital.aaron Over a year ago

@Paparazzi my answer has been updated and now works as OP intended.

SunKnight0 · Accepted Answer · 2018-01-10 21:40:22Z

-1

No matter what, you have to tell the database which value to pick for name. If you don't care an easy solution is to group:

SELECT gender, age, MIN(name) as name FROM mytable GROUP BY gender, age HAVING COUNT(*)=1

You can use any valid aggregate for name, but you have to pick something.

edited Jan 10, 2018 at 21:40

answered Jan 10, 2018 at 21:39

SunKnight0

3,3611 gold badge13 silver badges8 bronze badges

3 Comments

digital.aaron Over a year ago

This doesn't filter out the records with multiples.

SunKnight0 Over a year ago

Right, the wording didn't make sense to me. I will amend.

SunKnight0 Over a year ago

I don't have a testing platform at the moment but when all rows with multiple corresponding records are eliminated by the HAVING COUNT(*)=1, this is exactly what the result will be.

Collectives™ on Stack Overflow

Distinct on specific columns in SQL

3 Answers 3

Comments

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related