1

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.

Improve this question
1

2 Answers 2

Reset to default
3 
SELECT TOP 4 bookname 
FROM   bookstore 
WHERE  recommend = 'true' AND
       Year(pubdate)='2013'
ORDER  BY NEWID()
Improve this answer
7
  • Thanks. NEWID() was key here.
    – CoreLean
    Commented May 5, 2013 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 sqlfiddle.com/#!3/2b955/3
    – Vishwanath Dalvi
    Commented May 5, 2013 at 10:29
  • @V...I... what exactly do you mean?
    – John Woo
    Commented May 5, 2013 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
    Commented May 5, 2013 at 10:43
  • @JW웃 sqlfiddle.com/#!3/2b955/10 check this.
    – Vishwanath Dalvi
    Commented May 5, 2013 at 10:46
-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()

Improve this answer
10
  • 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 )
    – Ian Kenney
    Commented May 5, 2013 at 9:48
  • No, false, it will be called for every row.
    – Cthulhu
    Commented May 5, 2013 at 9:49
  • This sqlfiddle.com/#!3/d51df/7 suggests otherwise
    – Ian Kenney
    Commented May 5, 2013 at 9:54
  • 2
    Possibly it differs behavior in MySQL and MSSql 'cause it works in MySQL.
    – Cthulhu
    Commented May 5, 2013 at 9: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
    Commented May 5, 2013 at 9:55

Not the answer you're looking for? Browse other questions tagged or ask your own question.