Random selection of rows from sql result set?

Question

i have a query which will result 10 or 20 or any number number of rows. Below is the query.

select bookname from bookstore where recommend='true' and Year(pubdate)='2013'

This query can give me any number of rows.

But i have to show just 4 rows from the result. I can select top 4 but i would like select randomly so that every time same book name is not shown through out the year for the users when they visit.

Please help me how to get random rows from the result.

see http://stackoverflow.com/questions/848872/select-n-random-rows-from-sql-server-table — Ian Kenney, May 05 '13 at 09:51

score 3 · Accepted Answer · answered May 05 '13 at 09:42

3

SELECT TOP 4 bookname 
FROM   bookstore 
WHERE  recommend = 'true' AND
       Year(pubdate)='2013'
ORDER  BY NEWID()

answered May 05 '13 at 09:42

John Woo

258,903
69
498
492

Thanks. NEWID() was key here. – CoreLean May 05 '13 at 10:19
@JW웃 Hey using TOP with ORDER BY if we get 10 rows using where clause and then TOP 4 rows being selected but ORDER BY NEWID() is still applying on that 10 rows. so we need to make it as subquery Check Here http://sqlfiddle.com/#!3/2b955/3 – Vishwanath Dalvi May 05 '13 at 10:29
@V...I... what exactly do you mean? – John Woo May 05 '13 at 10:33
@JW웃 10 rows in a table, 6 rows filter out using where clause, then apply TOP 4 with ORDER BY NEWID() but still we're getting random rows including remaining 2 which filter out using TOP 4. hope it's clear. – Vishwanath Dalvi May 05 '13 at 10:43
@JW웃 http://sqlfiddle.com/#!3/2b955/10 check this. – Vishwanath Dalvi May 05 '13 at 10:46
@V...I... i get what you mean now. in your case, the op wants to get 4 random rows from the selected 6 rows. not the top 4 rows from the 6 result and reorder only on the 4 rows. – John Woo May 05 '13 at 10:46
@V...I... http://sqlfiddle.com/#!3/2b955/13 – John Woo May 05 '13 at 10:49

Cthulhu · Answer 2 · 2013-05-05T09:59:04.647

-2

select 
   bookname 
from 
   bookstore 
where 
   recommend='true' and 
   Year(pubdate)='2013' 
order by 
   rand() 
limit 4

Edit: For slq-server use newid() instead of rand()

edited May 05 '13 at 09:59

answered May 05 '13 at 09:42

Cthulhu

1,379
1
13
25

I don't think this will work, I believe rand() will be calculated once only in the query and will be the same for all rows : try this (http://sqlfiddle.com/#!3/d51df/1 ) – Ian Kenney May 05 '13 at 09:48
No, false, it will be called for every row. – Cthulhu May 05 '13 at 09:49
This http://sqlfiddle.com/#!3/d51df/7 suggests otherwise – Ian Kenney May 05 '13 at 09:54
2

Possibly it differs behavior in MySQL and MSSql 'cause it works in MySQL. – Cthulhu May 05 '13 at 09:55
@IanKenney Once in SQL server, for every row in MySQL (But the question is marked as SQL-Server so your statement is correct) – Magnus May 05 '13 at 09:55
Yes - it was a sqlserver question in this case – Ian Kenney May 05 '13 at 09:57
Well fine, gonna mark it in answer. – Cthulhu May 05 '13 at 09:57
@ Cthulhu, NewID() was key here. RAND () didn't work. Thanks though. – CoreLean May 05 '13 at 10:25
Yeah I was not aware of behavior difference of rand() between mysql and ms sql, sorry. – Cthulhu May 05 '13 at 10:36
@ Cthulhu: Please.. you Don't have to say sorry. You tried to help me. – CoreLean May 05 '13 at 12:18

Random selection of rows from sql result set?

2 Answers2