How can i select 1 random number from database thats ID is autoincrement field.I have 25,000[ID] records in database.Each every time select one new random ID. How can get it.
Asked
Active
Viewed 1,416 times
1
-
Please elaborate your question. – Nilambar Sharma Jun 20 '15 at 06:51
-
In my database table ID[auto increment],product name two fields then total product is extractly 30,000 records.Now 30,000 members are there and now each members show one PRODUCT randomly through ID without repeated ID. thats it.This information is enough for you. – venkatesh Jun 20 '15 at 07:08
2 Answers
1
You can use the following query to generate a random number between 0 and COUNT-1 of your table rows:
SELECT ROUND((RAND() * (SELECT COUNT(*) FROM mytable)-1))
Then use this number as an offset in the LIMIT clause of your query to get a random row from your table.
Using a prepared statement, you can do:
SET @offset = (SELECT ROUND((RAND() * (SELECT COUNT(*) FROM mytable)-1)));
PREPARE STMT FROM 'SELECT * FROM mytable LIMIT ?, 1';
EXECUTE STMT USING @offset;
Note that use of the prepared statement is mandatory since as documented in the manual:
LIMIT takes one or two numeric arguments, which must both be non-negative integer constants (except when using prepared statements).
Community
- 1
- 1
Giorgos Betsos
- 71,379
- 9
- 63
- 98
-
I was tried that query executes already generated value will show.how to overcome that repeating value – venkatesh Jun 20 '15 at 10:47
0
Use following sql(slow)
'SELECT * FROM tablename ORDER BY RAND() LIMIT 1'
or you can use(fast)
SELECT * FROM myTable, (SELECT FLOOR(MAX(myTable.id) * RAND()) AS randId FROM myTable) AS someRandId WHERE myTable.id = someRandId.randId
The inner SELECT gives you a random id in the right range. The outer SELECT looks for the right row in the table.
Ravi Kumar Mistry
- 1,063
- 1
- 13
- 24
-
Thanks for quick response@Ravi Kumar Mistry.I had already try that query ORDER BY RAND() makes performance issue.Any other way.. – venkatesh Jun 20 '15 at 06:46
-
i not runned the Query in the sql so it may be wrong, What i am trying to do in the last query is take 1 record and skip random number(less then total records) of record – Ravi Kumar Mistry Jun 20 '15 at 07:11
-
code working fine.I try to upload 10 id after that 10 times execute the query show the result 1,7,2,9,1,7,5,2,6,3.In the result 7 ,1,2 are repeated two times.I dont want repeat the same number – venkatesh Jun 20 '15 at 10:29
-
This is the nature of the Random no, no one knows what is next. To do that you need to handle it explicitly because after a query executes and return the data it don't know anything about the previous result set .So handle it in your code . – Ravi Kumar Mistry Jun 20 '15 at 10:43
-
you can maintain an int array in your code containing the last 100 id if new record ID matches any of the id in array discard the result set and run the query again.[TIP: keep the array small larger the array most probable the record found in the array] – Ravi Kumar Mistry Jun 20 '15 at 10:49
-
-
-
-
-
online lucky draw system total 10000 users and 10000 different products..each user onclick show 1 product that it – venkatesh Jun 20 '15 at 11:23
-
Add an extra boolean column 'Shown' in database if product is shown mark that column true. and write a query which filter all the not shown data and then apply this random select on that result set. – Ravi Kumar Mistry Jun 20 '15 at 11:39
-
-
-
getting error, select ID,productname, from products where Pcount='" + strue + "',select RAND() as ID – venkatesh Jun 20 '15 at 13:25