Random select from database
Andrew
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.
Any help would be much appreciated.
"j.keßler"
Hash: SHA1
Hello,
I don't think that this can be done with one query.
First you need to get the towns and the for each of them run a query with
the RAND function and limit the result to 1
eg.
(...)ORDER BY RAND() LIMIT 1;
regards,
j.ke�ler
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.11 (GNU/Linux)
iEYEARECAAYFAkpkfXwACgkQE++2Zdc7EtclAQCfRSOAYvGpyNENqpkXZ346wF7S
aIkAn16KT0uhFcjfDaDuf9FltonI1RLs
=/FEW
-----END PGP SIGNATURE-----
ThanksButNo
> Andrew 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.
>
> > Any help would be much appreciated.
>
> Hello,
>
> I don't think that this can be done with one query.
>
> First you need to get the towns and the for each of them run a query with
> the RAND function and limit the result to 1
> eg.
> (...)ORDER BY RAND() LIMIT 1;
>
No no, I think you're on to it -- it can be done in one query:
SELECT The, Columns, I, Want
FROM MyTable
WHERE MyTown = 'TheTownIWant'
ORDER BY RAND() LIMIT 1;
That works.
/:-/
Jerry Stuckle
Read the requirements again. That gets it for ONE town - not "all listed".
I tend to agree with j.ke�ler (as usual) on this.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
==================
ThanksButNo
You are correct, and I had re-read the requirements.
But I didn't have anything to add (other than "oops") so
I didn't post. I figured the OP would be bright enough to
recognize it if my query didn't work for him.
If I get another few moments free I might try a solution
using stored procedures. Otherwise, the best idea is to
run something at the application layer, which would pass
the town data into that query with the single town.
/:-/
"j.keßler"
Hash: SHA1
thank you.
regards,
j. ke�ler
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.11 (GNU/Linux)
iEYEARECAAYFAkplbq0ACgkQE++2Zdc7Etd5aACcDB/HS9su3iWFxgN5hgVJdUtf
MtEAn2bQZlWAslwFVZ2zc53yKQkszUaY
=r70U
-----END PGP SIGNATURE-----
Andrew
Thank you for your responses. I've now used multiple queries and have
the desired output.
Lennart
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> I don't think that this can be done with one query.
>
One possibility is to create a view and use that. Assuming a table
like:
create table T ( a int not null, b char(1) not null, primary key
(a,b) );
insert into T (a,b) values (1, 'a'),(1, 'b'), (2,'c'),(2, 'd'),(2,'e'),
(3,'f');
and a view:
create view V as select a,b,rand() as rand_nr from T;
select a,b from V V1
where rand_nr = (select min(rand_nr) from V V2 where V1.a = V2.a);
Using thingies from SQL99 (cannot verify whether any of these is
supported in mysql since I don't have an installation at hand) removes
the need for an explicit view:
1. Using row_number function (SQL99)
select a,b from (
select a,b,
row_number() over (partition by a order by rand_nr) as
rand_order
from (
select a,b,rand() as rand_nr from T
)
) where rand_order = 1;
2. Using a common table expression (SQL99):
with X (a,b,rand_nr) as (
select a,b,rand() from T
)
select a,b from X X1
where rand_nr = (select min(rand_nr) from X X2 where X1.a = X2.a);
/Lennart
toby
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
Jerry Stuckle
> On 20 Juli, 16:21, "j.ke�ler" <m...@bananas-playground.net> wrote:
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
> [...]
>> I don't think that this can be done with one query.
>>
>
> One possibility is to create a view and use that. Assuming a table
> like:
>
> create table T ( a int not null, b char(1) not null, primary key
> (a,b) );
> insert into T (a,b) values (1, 'a'),(1, 'b'), (2,'c'),(2, 'd'),(2,'e'),
> (3,'f');
>
> and a view:
>
> create view V as select a,b,rand() as rand_nr from T;
>
> select a,b from V V1
> where rand_nr = (select min(rand_nr) from V V2 where V1.a = V2.a);
>
Interesting - but still does not solve the problem of getting one value
for each town.
> Using thingies from SQL99 (cannot verify whether any of these is
> supported in mysql since I don't have an installation at hand) removes
> the need for an explicit view:
>
> 1. Using row_number function (SQL99)
>
> select a,b from (
> select a,b,
> row_number() over (partition by a order by rand_nr) as
> rand_order
> from (
> select a,b,rand() as rand_nr from T
> )
> ) where rand_order = 1;
>
> 2. Using a common table expression (SQL99):
>
> with X (a,b,rand_nr) as (
> select a,b,rand() from T
> )
> select a,b from X X1
> where rand_nr = (select min(rand_nr) from X X2 where X1.a = X2.a);
>
> /Lennart
>
None of which are available in MySQL.
Lennart
[...]
>
> Interesting - but still does not solve the problem of getting one value
> for each town.
Why?
/Lennart
Jerry Stuckle
Please show where you can get random (non-repeatable) results for 5
towns from this query in MySQL.
Lennart
> Lennart wrote:
> > On 22 Juli, 04:25, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> > [...]
> >> Interesting - but still does not solve the problem of getting one value
> >> for each town.
>
> > Why?
>
> > /Lennart
>
> Please show where you can get random (non-repeatable) results for 5
> towns from this query in MySQL.
I'm not sure what your objection is (is it the seed for random, or is
it the number of evaluations of rand(), or is it something else?) I
cannot verify with mysql for the moment, the following is from db2 9.5
luw:
[lelle ~]$ db2 "select * from V"
TOWN WHATEVER RAND_NR
----------- -------- ------------------------
1 a +5.98088777436916E-001
1 b +7.09870838890723E-001
2 c +5.92889677077946E-001
2 d +3.65484240635058E-002
2 e +6.72886075765307E-001
3 f +6.85485736786148E-002
4 g +8.48067710571023E-001
4 h +9.23962997237203E-001
4 i +2.67720297103617E-002
5 j +1.82954710993429E-001
5 k +2.18902509761463E-002
11 record(s) selected.
[lelle ~]$ db2 "select town, whatever from V V1
where rand_nr = (select min(rand_nr) from V V2 where V1.town =
V2.town)"
TOWN WHATEVER
----------- --------
1 b
2 e
3 f
4 g
5 k
5 record(s) selected.
[lelle ~]$ db2 "select town, whatever from V V1
where rand_nr = (select min(rand_nr) from V V2 where V1.town =
V2.town)"
TOWN WHATEVER
----------- --------
1 a
2 d
3 f
4 g
5 j
5 record(s) selected.
[lelle ~]$ db2 "select town, whatever from V V1
where rand_nr = (select min(rand_nr) from V V2 where V1.town =
V2.town)"
TOWN WHATEVER
----------- --------
1 a
2 c
3 f
4 h
5 k
5 record(s) selected.
[lelle ~]$ db2 "select town, whatever from V V1
where rand_nr = (select min(rand_nr) from V V2 where V1.town =
V2.town)"
TOWN WHATEVER
----------- --------
1 b
2 e
3 f
4 h
5 k
5 record(s) selected.
/Lennart
[...]
Jerry Stuckle
Yes, that is different queries for different towns. Read the
requirements. He wants to have ONE query which returns ONE random
address from MULTIPLE TOWNS.
Your query does not meet his requirements.
Lennart
Well, in that case I have misunderstood. When the OP stated:
"What I want to do is pull out one random address for each town
listed."
I took that as if he wanted a result set with 8 addresses, one for
each town.
/Lennart
Lennart
I'm not sure what you mean by this
/Lennart
[...]
Jerry Stuckle
OK, I see where you're going now. But your query gives an empty set in
MySQL because the rand() function is executed each time you access V.
So when you try to compare random numbers you get no match.
Lennart
Ah, ok. I see
/Lennart