CODEWITHSUNDEEP
sql
Fischer Ludrian
Fischer Ludrian

Reputation: 677

Get one random row of large SQL table

I want to use the StackExchange Data Explorer to get 1 random Java question that is not closed:

DECLARE @tagId int;

SELECT  @tagId = Id
FROM    Tags         
WHERE   TagName = 'java';

SELECT TOP 1 * FROM posts p join PostTags pt on pt.PostId = p.Id
WHERE pt.TagId = @tagId
AND p.PostTypeID = 1
AND p.ClosedDate IS NULL
Order by newid();

However, I get always the same result. Is this because the query is cached? Would that work without caching?

Upvotes: 0

Views: 51

Answers (2)

Charles Bretana
Charles Bretana

Reputation: 146499

Even though we use the word random for these kinds of things, it remains the case that computers, by themselves cannot do anything randomly.

That's why they describe these computer functions as "pseudo-random". Anytime you run the same thing multiple times, (unless it is dependent on some outside factor, like the current time or something) it will always generate the same result.

If you really want the thing to generate a different value everytime, then you must use a variant of the random function (most languages have this functionality), that takes a "seed" value as an input parameter, and use something like the current time (in ticks or milliseconds, or something like that) as the "seed".

Upvotes: 1

Anup Agrawal
Anup Agrawal

Reputation: 6669

SQL is first applying the Top 1 clause and then doing the order by thats the reason you are getting the same result.

Try something like this:

DECLARE @tagId int;

SELECT  @tagId = Id
FROM    Tags         
WHERE   TagName = 'java';

;WITH CTE AS 
(SELECT * FROM posts p join PostTags pt on pt.PostId = p.Id
WHERE pt.TagId = @tagId
AND p.PostTypeID = 1
AND p.ClosedDate IS NULL
Order by newid();
) 

SELECT TOP 1 * FROM CTE

OR Try this:

DECLARE @tagId int;

SELECT  @tagId = Id
FROM    Tags         
WHERE   TagName = 'java';

SELECT TOP 1 * FROM 
(SELECT TOP 100 PERCENT * FROM posts p join PostTags pt on pt.PostId = p.Id
WHERE pt.TagId = @tagId
AND p.PostTypeID = 1
AND p.ClosedDate IS NULL
Order by newid();
) A

Upvotes: 1

Related Questions