On Jul 20, 10:00 am, Andrew <andrew
...@gmail.com> wrote:
> Apologies if my question is really simple but I've been racking my
> brain for hours and can't solve it.
> I have a database table with address details in consisting of
> resident, street, town, postcode.
> This database has 12478 records in with addresses from 8 different
> towns. What I want to do is pull out one random address for each town
> listed. I can get it to pull out the first or last address for each
> town but need them to be random.
Here is one way. I can't vouch for its efficiency. :)
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)
(repeated)
+----+--------------------+-----+--------------------------
+-------------+-----+
| n | rnd | id | street |
town | idx |
+----+--------------------+-----+--------------------------
+-------------+-----+
| 12 | 0.476556181420159 | 6 | 2000 W Dimond Blvd |
Anchorage | 6 |
| 10 | 0.0172840263855495 | 31 | 810 Saint Vincents Dr |
Birmingham | 1 |
| 11 | 0.656751618400916 | 60 | 9 Du Rhu Drive |
Mobile | 8 |
| 16 | 0.231906043581577 | 118 | 1060 E Ray Rd |
Chandler | 4 |
| 10 | 0.189275393438782 | 153 | 4701 W. Thunderbird Road |
Glendale | 2 |
| 18 | 0.250658867182824 | 180 | 2151 N Power Rd |
Mesa | 5 |
| 63 | 0.685468186331418 | 248 | 455 N. 3rd Street |
Phoenix | 44 |
| 26 | 0.675364360842262 | 292 | 10135 E. Via Linda |
Scottsdale | 18 |
| 16 | 0.320417275950701 | 319 | 926 E Broadway |
Tempe | 6 |
| 35 | 0.575993327960366 | 351 | 1209 W. Irvington Road |
Tucson | 21 |
| 19 | 0.91871484268337 | 454 | 2200 Panama Lane |
Bakersfield | 18 |
| 15 | 0.865594260269398 | 519 | 1190 Alameda Avenue |
Burbank | 13 |
+----+--------------------+-----+--------------------------
+-------------+-----+
12 rows in set (0.16 sec)
> Any help would be much appreciated.
--Toby
Engineer, Open Query (http://openquery.com)
Affordable Training and ProActive Support for MySQL & related
technologies
Follow our blog at http://openquery.com/blog/
OurDelta: free enhanced builds for MySQL @ http://ourdelta.org
