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

Question

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)?

If `BookID` is a primary key, how do you get any duplicate values at all? — Gordon Linoff, Feb 24 '14 at 19:59

score 4 · Accepted Answer · answered Feb 24 '14 at 19:49

4

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.

answered Feb 24 '14 at 19:49

Gordon Linoff

1,242,037
58
646
786

No. It doesn't work. Error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified. – Farshid Feb 24 '14 at 19:50
@Farshid . . . It looked funny after I wrote it. The answer is fixed with `group by`. – Gordon Linoff Feb 24 '14 at 19:53
You removed DISTINCT . Won't it affect uniqueness of records? – Farshid Feb 24 '14 at 19:57
@Farshid . . . No. The `group by` removes duplicate values. – Gordon Linoff Feb 24 '14 at 19:58
Thanks Gordon and Zane – Farshid Feb 24 '14 at 19:59

ajcarland90 · Answer 2 · 2014-02-24T20:04:19.950

3

Or if you still want to use NEWID, just make a distinct list before assigning the new id:

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

edited Feb 24 '14 at 20:04

answered Feb 24 '14 at 19:57

ajcarland90

31
3

No I don't want to mention NEWID() in my columns. It makes results to have douplicates, – Farshid Feb 24 '14 at 19:58
Ok if you remove the NEWID() from the top row in the select statement, it will still assign each row a NEWID and return random unique values. – ajcarland90 Feb 24 '14 at 20:01
I made the adjustment to the code above - hope that helps – ajcarland90 Feb 24 '14 at 20:04

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

2 Answers2