0
SELECT COUNT(*) WHERE location='new york'
//fetch rows
$rand=rand(0,$rows-1);

SELECT ... WHERE location='new york' LIMIT ".$rand.", 2

I have query try to select 2 random rows from db, what I did is

I count total rows first than use rand() to random the page

any suggestion, or are any better way to do this?

Ben
  • 2,562
  • 8
  • 37
  • 62
  • 1
    Your solution does not pick two random rows. It only picks one random row and the row that follows it. Also if `$rand` happens to be the last item, you only get one row. – Dwayne Towell Feb 23 '14 at 01:10
  • Duplicate question? http://stackoverflow.com/questions/19412/how-to-request-a-random-row-in-sql, http://stackoverflow.com/questions/580639/how-to-randomly-select-rows-in-sql, http://stackoverflow.com/questions/4329396/mysql-select-10-random-rows-from-600k-rows-fast – Mr. Radical Feb 23 '14 at 01:34

2 Answers2

2

You can try like this 

SELECT ... WHERE location='new york' ORDER BY RAND() LIMIT 2
Minhaz
  • 446
  • 5
  • 7
1

If it was all about performance, i would do it this way (without reading all rows)

SELECT FLOOR(RAND() * COUNT(*)) AS offset1,FLOOR(RAND() * COUNT(*)) AS offset2 FROM {table} WHERE location='new york'
SELECT * FROM {table} LIMIT $offset1, 1
SELECT * FROM {table} LIMIT $offset2, 1

I know, 3 queries but the two of them are quite fast