select random rows from table

[kamurj]

hi everyone! how can i select random results from a table and limiting the result to 2?

normally i would need something like this

select from amazon where cat='$cat' limit 2;

but i need it to select randomly and that's what i dont know how to do. thank you!

[Anon]

Find out how many rows you have to choose from (SELECT count(*) as range from amazon where cat='$cat'), pick two random numbers $row1 and $row2 between 0 and $range-1 inclusive (make sure they're different, if $row2==$row1, try picking $row2 again), then do two selects:

"select * from amazon where cat='$cat' offset $row1 limit 1"

"select * from amazon where cat='$cat' offset $row2 limit 1"

Better is if your rows have a unique id (which I'll assume is called 'id'). Select all the ids from the table (where cat='$cat'), pick two at random ($id1 and $id2) and then query

"select * from amazon where id=$id1 or id=$id2";

If your DBMS has its own random number generator, it may make more sense to exploit that.

[kamurj]

sorry but too complicated for me. i need an easier solution the taks being to select 2 random rows from the table amazon where cat='$cat'

[Anon]

I don't think it will get much simpler.

[Anon]

Hayley's solution is probably the best, and I agree, it won't get simpler.