How do I select a fixed number of rows for each group?

Here is some example data in a mysql table

a   b   distance
15  44  250
94  31  250
30  41  250
6   1   250
95  18  250
72  84  500
14  23  500
55  24  500
95  8   500
59  25  500
40  73  500
65  85  500
32  50  500
31  39  500
22  25  500
37  11  750
98  39  750
15  57  750
9   22  750
14  44  750
69  22  750
62  50  750
89  35  750
67  65  750
74  37  750
52  36  750
66  53  750
82开发者_开发问答  74  1000
79  22  1000
98  41  1000

How do I query this table such that I get 2 rows per distance selected at random?

A successful query will produce something like

   a    b   distance
    30  41  250
    95  18  250
    59  25  500
    65  85  500
    15  57  750
    89  35  750
    79  22  1000
    98  41  1000

Use:

SELECT x.a,
       x.b,
       x.distance
  FROM (SELECT t.a,
               t.b,
               t.distance
               CASE 
                 WHEN @distance != t.distance THEN @rownum := 1 
                 ELSE @rownum := @rownum + 1 
               END AS rank,
               @distance := t.distance
          FROM TABLE t
          JOIN (SELECT @rownum := 0, @distance := '') r
      ORDER BY t.distance --important for resetting the rownum variable) x
 WHERE x.rank <= 2
ORDER BY x.distance, x.a

One way would be to use union. Like so:

(SELECT a, b, distance FROM table WHERE distance = 250 LIMIT 2 ORDER BY RAND())
UNION
(SELECT a, b, distance FROM table WHERE distance = 500 LIMIT 2 ORDER BY RAND())
...
ORDER BY distance

I can think of a way of getting one of each with one query using distinct =/, but like I said that would only bring you one.

I wonder if this will work?

SELECT 
   a,b,distance 
FROM YourTable t2
   WHERE ROW(a,b,distance) IN 
   (
      SELECT a,b,distance FROM YourTable t1 
      WHERE t1.distance=t2.distance ORDER BY RAND() LIMIT 2
   )

EDIT: unfortunately not. LIMIT is not supported in a subquery.