random select from table [duplicate]

This question already has answers here: Closed 12 years ago.

Possible Duplicate:

How to request a random row in SQL?

           开发者_StackOverflow中文版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

---

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

---

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.

1. One query to retrieve a list of schools.
2. 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.

---

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