[SOLVED] How to prevent duplicated records when applying ORDER BY NEWID() to fetch them randomly?

How to prevent duplicated records when applying ORDER BY NEWID() to fetch them randomly?

I tried using the solution provided in 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)?

Solution

You definitely don't want to add newid() to each row. That will undo the distinct. Instead, use group by with order bynewid()`:

  SELECT top 4 b.BookID, b.Authors, b.ShortTitle              
  FROM Books b inner join
       Publishers p
       on b.PublisherID = p.PublisherID
  GROUP BY b.BookId, b.Authors, B.ShortTitle
  ORDER BY NEWID();

It will work fine. You can order by values that are not in the select list.