Write a query that remove duplicate that have a certain value

Question

This is not the question but it's similar.

First i solved the query by I using unary join and calculated what required (distance) now I want to count how many pairs have the shortest distance without duplication. Example

ID   ID   Distance
1    2    0.1
2    1    0.1
3    5    0.13
4    7    0.1

The answer should be 2 but I get 3 as I don’t know how to remove the duplication between 2,1 and 1,2.

Would anyone please help me the answer should be (number of pairs , shortest distance )

Show your original query (the one that generate those four rows of sample data). It may be that you can reformulate that query to avoid the duplication (possibly simply be enforcing that id1 must be less than id2) — MatBailie
– MatBailie, Commented Apr 27, 2021 at 19:39

Gordon Linoff · Accepted Answer · 2021-04-27 19:35:22Z

1

Based on your sample data, you can use count(distinct) but with a twist:

select distance, count(distinct least(id1, id2), greatest(id1, id2))
from t
group by distance
order by distance
limit 1;

answered Apr 27, 2021 at 19:35

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

user870251 Over a year ago

How can I do it using where condition rather than least greatest ?

Gordon Linoff Over a year ago

@user870251 . . . You could use case instead, but I see no reason for that. This answers the question that you asked.

Collectives™ on Stack Overflow

Write a query that remove duplicate that have a certain value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related