Is it possible to retrieve random rows from table X where flags==0? Using MySql and C#
+4
A:
SELECT *
FROM X
WHERE flags = 0
ORDER BY rand()
LIMIT 1
This retrieves 1 random row. Replace 1 by N to get N random rows.
inflagranti
2010-06-23 15:20:46
If I am not wrong, should this line always order by first or second row? Rand() will always return a number between 0 or 1.. so will order by column 0 or 1? Not sure
cad
2010-06-23 15:48:32
select Rand() gets me 0.NUM BUT using `ORDER BY rand()` does get me completely random results (i just tested with 4 rows)
acidzombie24
2010-06-23 15:52:54
According to the MySQL manual:Returns a random floating-point value v in the range 0 <= v < 1.0.So it will not only return 0 or 1, but any value inbetween (floating point).
inflagranti
2010-06-23 15:56:56
inflagranti: ORDER BY rand() is still blowing my mind, i cant believe how simple it is.
acidzombie24
2010-06-23 16:01:07
Why? It defines exactly what you want: a randomly ordered table. And then you fetch some desired N rows from that table (it being random, it doesn't matter that those are the top rows).
inflagranti
2010-06-23 16:07:35
lol. It does exactly what i want. But... i cant believe what great results i am getting (these appear to be very random) and how easy the sql was! :D
acidzombie24
2010-06-23 16:14:58
acid: keep in mind, though, that's it's horribly inefficient since you have to forcibly go over *all* rows in the table. For your average tiny database it probably won't matter but get into row counts of a few million and you'll surely have fun.
Joey
2010-06-23 18:01:31