CODEWITHSUNDEEP
mysqlsqldistinct
Karamellwuerfel
Karamellwuerfel

Reputation: 137

Just get one data if duplicate (not DISTINCT)

I have the following result from a query in mysql (headers: depicao, arricao):

EDDH, EDDK
EDFH, EDDL
EDDS, EDDH
EDDK, EDDH

My problem is now, that I just want one of the rows, IF the data exist in the correlation "EDDH - EDDK" AND "EDDK - EDDH".

The query for this result is something like this:

SELECT DISTINCT p.depicao, p.arricao FROM xyz WHERE xxyyzz = 1

Thanks for your help!

Upvotes: 0

Views: 44

Answers (2)

Barmar
Barmar

Reputation: 782488

Order the columns in a consistent way with GREATEST and LEAST, then use SELECT DISTINCT to remove duplicates.

SELECT DISTINCT GREATEST(depicao, arricao) as col1, LEAST(depicao, arricao) AS col2
FROM xyz
WHERE ...

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270993

I think row_number() does what you want:

select p.*
from (select p.*,
             row_number() over (partition by least(p.depicao, p.arricao), greatest(p.depicao, p.arricao) order by rand()) as seqnum
      from p
     ) p
where seqnum = 1;

You can add additional filtering conditions in the subquery, which you seem to have in your query but not your question.

Upvotes: 0

Related Questions