random select from table

Question

Possible Duplicate:
How to request a random row in SQL?

           I have table:
           name,age,school
           jane,15,zu
           peter,16,zu
           john,15,stu
           Tomas,15,kul
           viera,17,stu
           tibor15,zu

I want select from this table 1 person (randomly) per school

dupe http://stackoverflow.com/questions/19412/how-to-request-a-random-row-in-sql — CheeseConQueso, Dec 03 '10 at 13:25

Pradeep Singh · Answer 1 · 2010-12-03T14:35:03.013

0

select * from table  group by school order by rand()

edited Dec 03 '10 at 14:35

answered Dec 03 '10 at 13:28

Pradeep Singh

3,582
3
29
42

Not working,still the same record per school – lubo Dec 03 '10 at 13:43
I removed limit pls check again – Pradeep Singh Dec 03 '10 at 14:05
Still not working for me.After run this query:order of records are not the same,but person is still same: – lubo Dec 03 '10 at 14:17
for example:1)peter,16,zu;john,15,stu;Tomas,15,kul 2)john,15,stu;peter,16,zu;Tomas,15,kul – lubo Dec 03 '10 at 14:18
i want this:1)peter,16,zu;john,15,stu;Tomas,15,kul 2)jane,16,zu;viera,15,stu;tomas,15,kul – lubo Dec 03 '10 at 14:20
select * from table group by school, person order by rand() – Pradeep Singh Dec 03 '10 at 14:20
why i must group person? ...result give me more unneeded records. – lubo Dec 03 '10 at 14:33
it is not possible using SQL,i think. I try resolve it with array in PHP – lubo Dec 03 '10 at 14:43

score 0 · Answer 2 · answered Dec 03 '10 at 15:32

It's terribly innefficient since it's ORDERing the entire table randomly, and then GROUPing on a potentially huge unindexed temporary table, but this works.

SELECT * 
FROM (
     SELECT * 
     FROM  my_table 
     ORDER BY RAND()
     )a
GROUP BY school

It would likely be more wise to break it down.

One query to retrieve a list of schools.
For each school, one query to get a random student for that school.

See this post for some slick tricks on how to get single random values efficiently.

Works perfectly.Tthat is exactly what i looking for. Thank you Riedsio — lubo, Dec 03 '10 at 18:01

score -2 · Answer 3 · answered Dec 03 '10 at 13:27

-2

If you are using php (as the tag suggests), run SELECT * FROM table; then generate a random number based on the number of results in the query. For example:

i = floor(random()*query.length);

Then seek to the record indicated seek(i) and viola, you have a random entry =)

You'll have to use your own knowledge/documentation for the exact syntax, I haven't touched PHP in a while, but the logic is sound.

Gary

answered Dec 03 '10 at 13:27

gsteinert

1,324
2
10
13

Not very useful when there's more than a few rows in the database... – NOtherDev Dec 03 '10 at 13:30
As @A said, this is a terrible idea. On large tables this will be very slow, and create unneeded load on the database. – Erik Dec 03 '10 at 13:36

random select from table

3 Answers3