Random selection of rows from sql result set? [duplicate]

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 stackoverflow.com/questions/848872/…

Ian Kenney
– Ian Kenney

2013-05-05 09:51:09 +00:00
Commented May 5, 2013 at 9:51 — Ian Kenney
– Ian Kenney, Commented May 5, 2013 at 9:51

John Woo · Accepted Answer · 2013-05-05 09:42:14Z

3

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

answered May 5, 2013 at 9:42

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

CoreLean Over a year ago

Thanks. NEWID() was key here.

Vishwanath Dalvi Over a year ago

@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 sqlfiddle.com/#!3/2b955/3

John Woo Over a year ago

@V...I... what exactly do you mean?

Vishwanath Dalvi Over a year ago

@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 Over a year ago

@JW웃 sqlfiddle.com/#!3/2b955/10 check this.

|

Cthulhu · Accepted Answer · 2013-05-05 09:59:04Z

-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 5, 2013 at 9:59

answered May 5, 2013 at 9:42

Cthulhu

1,3711 gold badge13 silver badges26 bronze badges

10 Comments

Ian Kenney Over a year ago

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 (sqlfiddle.com/#!3/d51df/1 )

Cthulhu Over a year ago

No, false, it will be called for every row.

Ian Kenney Over a year ago

This sqlfiddle.com/#!3/d51df/7 suggests otherwise

Cthulhu Over a year ago

Possibly it differs behavior in MySQL and MSSql 'cause it works in MySQL.

Magnus Over a year ago

@IanKenney Once in SQL server, for every row in MySQL (But the question is marked as SQL-Server so your statement is correct)

|

Collectives™ on Stack Overflow

Random selection of rows from sql result set? [duplicate]

2 Answers 2

7 Comments

10 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

10 Comments

Linked

Related