Remove duplicate results, while taking a random value for one column

Question

What i have is the following as result of a query, as you can see it has some duplicates except for the related column. What i want is to remove the duplicates while the related column gets a random value from possible values the related id has.

So something like:

 id  | name | related
-----+------+-----
   1 | bart | 2
   1 | bart | 5
   1 | bart | 7
   2 | john | 3
   2 | john | 4
   3 | mary | 2

Becomes something like:

 id  | name | related
-----+------+-----
   1 | bart | 5
   2 | john | 3
   3 | mary | 2

Prix · Accepted Answer · 2013-08-01 19:37:30Z

1

You can use a sub-query with ORDER BY RAND() and then use GROUP BY name on the result:

SELECT id,name,related 
  FROM (
           SELECT * 
             FROM myTable 
         ORDER BY RAND()
       ) AS result 
GROUP BY name

Live DEMO.

answered Aug 1, 2013 at 19:37

Prix

19.6k16 gold badges79 silver badges136 bronze badges

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

4 Comments

Bananenspin Over a year ago

Could you explaining the workings of this as im trying to understand what's happening and why its working. As i have to implement this into a query thats a bit more complex that already knows two left joins. I simply dont know how to implement this into it.

Prix Over a year ago

The sub-query select your table into a random order using the function rand(), the result is used on the outer query, the outer query will group the result by name so you will get the results no duplicated names and in random order every time you reuse it. To put it simple you could say the sub-query creates what you could say a temporary table of that select.

Bananenspin Over a year ago

Its rather easy after taking a second look at it! Got it working now, the only problem i had is that i have two left joines in my sub-query and it would not recognize table alliases, so i had to type all the column names out and give them aliasses as well. Thanks!

Prix Over a year ago

@Bananenspin you're welcome. If you had posted your query I could have helped :) the more information you provide closer to your own issue the more exact the answer you will can be.

missdefying · Accepted Answer · 2013-08-01 20:25:16Z

0

If you just want the first related number and not necessarily a truly random related number, you can do this:

select distinct t.id, t.name, t2.related 
from tester t 
inner join 
    (select id, related from tester group by id, name) t2 on t.id=t2.id

answered Aug 1, 2013 at 20:25

missdefying

1514 bronze badges

1 Comment

Bananenspin Over a year ago

No unfortunately in my case i'm in need of one of the related numbers and not just the first result.

Collectives™ on Stack Overflow

Remove duplicate results, while taking a random value for one column

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related