Journal toby's Journal: SQL: random member of each of many groups
mysql> select T.n, T.rnd, L.id, L.street, L.town, count(R.id) as idx
from (
select count(*) as n, rand() as rnd, town
from addr
group by town
) T
join addr L on L.town = T.town
join addr R on R.town = L.town and R.id <= L.id
group by L.id
having ceil(T.n*T.rnd) = idx;
+----+---------------------+-----+---------------------------------
+-------------+-----+
| n | rnd | id | street |
town | idx |
+----+---------------------+-----+---------------------------------
+-------------+-----+
| 12 | 0.00842807908396058 | 1 | 601 West 5th Avenue |
Anchorage | 1 |
| 10 | 0.650273804227145 | 37 | 9190 Parkway East |
Birmingham | 7 |
| 11 | 0.226084814617489 | 55 | 3494 BEL AIR MALL |
Mobile | 3 |
| 16 | 0.179602691139656 | 117 | 4940 S Gilbert Rd |
Chandler | 3 |
| 10 | 0.219759042579456 | 154 | 7123 N. 138th Ave. |
Glendale | 3 |
| 18 | 0.559987170211959 | 187 | 2832 North Power Road |
Mesa | 11 |
| 63 | 0.14065875405507 | 213 | 340 East McDowell Rd |
Phoenix | 9 |
| 26 | 0.0233322903731207 | 275 | 15660 N Frank Lloyd Wright Blvd |
Scottsdale | 1 |
| 16 | 0.694685220434037 | 325 | 5000 Arizona Mills Circle |
Tempe | 12 |
| 35 | 0.403429261784469 | 345 | 3901 W Ina Rd |
Tucson | 15 |
| 19 | 0.933090678353878 | 454 | 2200 Panama Lane |
Bakersfield | 18 |
| 15 | 0.45516563714963 | 513 | 4000 Warner Blvd |
Burbank | 7 |
+----+---------------------+-----+---------------------------------
+-------------+-----+
12 rows in set (0.16 sec)
SQL: random member of each of many groups More Login
SQL: random member of each of many groups
Slashdot Top Deals