How do I select one record per group and left join with another table?

Question

I have one table with two location IDs (pointa and pointb) and how far apart they are. Multiple pairs can be the same distance apart.

Example data:

pointa pointb distance

1 2 250

3 4 250

4 5 250

.....

6 8 500

10 12 500

13 17 500

I want to select one pair from each distance class (one from 250, one from 500 and so on) and join those to another table which contain attributes for that location.

If I were to write this as an algorithm, it would go:

Select one pair at random from each distance class from table distance join with table data based on distance.pointa=data.location. Then do the same for pointb such that pointb=data.location

So after the join, I end up with:

pointa pointb data_a data_b

1 2 234.5 440.2

Does anyone have ideas on how I can achieve this?

For now I am doing this using PHP (looking up attributes for a, then b, and then updating a new table. Clearly this is inefficient and I want to learn a better way to do this directly in MySQL.

tia.

Answer 1

getting random rows in SQL can be less than efficient. I'd try something like:

SELECT distance, GROUP_CONCAT(pointa) a, GROUP_CONCAT(pointb) b FROM mytable
GROUP BY distance;

Then in PHP,

while($distance_points= $result->fetch_assoc()){
  $distance=$distance_points['distance'];
  $as=explode($distance_points['a']);
  $random_a=array_rand($as,1);
  $bs=explode($distance_points['b']);
  $random_b=$bs[array_search($as,$random_a)]; //to get the pair assuming points don't repeat

  echo "$distance $random_a $random_b";

}

ETA: Since you have too much data for GROUP_CONCAT, a multiquery solution might work better. Something like:

SELECT DISTINCT distance FROM mytable ORDER BY distance; //get an array with the possible distances

foreach($distances as $d)
  $q="SELECT distance, a, b FROM mytable WHERE distance=$d ORDER BY RAND() LIMIT 1";
  //run query, retrieve single result and output or store
}

Since you want to get extra info about your locations a and b, you could add that join to $q. Something like:

SELECT distance, a, li1.gps, b, li2.gp2
FROM mytable m 
  JOIN location_info li1 ON (m.a=li1.id) 
  JOIN location_info li2 ON (m.b=li2.id) 
WHERE distance=$d 
ORDER BY RAND() LIMIT 1