Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I tried using the solution provided in http://stackoverflow.com/questions/1117761/sql-query-to-return-rows-in-random-order to fetch random records in my query. But I have to add NEWID() to the list of columns I want to fetch or otherwise I will not be able to add ORDER BY NEWID() . Unfortunately it makes my resultset to contain duplicate records.

For more clarification, this query makes my results to have duplicates due to existence of NEWID() among requested columns:

SELECT distinct top 4 
              Books.BookID,
              Books.Authors,                  
              Books.ShortTitle,               
              NEWID()                 
  FROM Books 
  inner join Publishers on Books.PublisherID = Publishers.PublisherID

  ORDER BY NEWID()

How can I overcome this issue of not fetching unique records (Here BookID is PK)?

share|improve this question

closed as off-topic by Jack Douglas Feb 25 at 7:44

If this question can be reworded to fit the rules in the help center, please edit the question.

1  
Why do you need distinct? Isn't PublisherID PK in Publisher? –  Mikael Eriksson Feb 24 at 20:17
2  
You should not have to include NEWID() in your select. –  Digital Chris Feb 24 at 20:18
    
@DigitalChris you do with DISTINCT. –  Aaron Bertrand Feb 24 at 20:19
3  
@DigitalChris I guess so, MySQL is pretty loosey-goosey when it comes to rules and standards. –  Aaron Bertrand Feb 24 at 20:23
1  
Please do not cross post: stackoverflow.com/questions/21997474/… One question in one site is enough. –  ypercube Feb 25 at 3:29

1 Answer 1

Try this, using a CTE as an intermediate result set:

WITH Randomized AS(
SELECT      Books.BookID,
              Books.Authors,                  
              Books.ShortTitle,               
              NEWID() as 'ID'
  FROM Books 
  inner join Publishers on Books.PublisherID = Publishers.PublisherID
)

SELECT -- TOP 4
     BookID, Authors, ShortTitle, MAX(ID)
FROM Randomized
GROUP BY BookID, Authors, ShortTitle
ORDER BY MAX(ID)

This will still get you one record per bookId, but a random order.

share|improve this answer
    
Need something about 4 in there at some point. I was crafting an answer using ROW_NUMBER() but I'm still unclear what exactly is trying to be randomized / unique-ified. Does the OP only want to see any publisher once? –  Aaron Bertrand Feb 24 at 20:20
    
I thought about the 4 but wasn't sure on the requirements. I mean we can put that in the second select... –  JNK Feb 24 at 20:21

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