How to return records Randomly

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to return records Randomly

Author

Topic

azharrahi
Starting Member

44 Posts

Posted - 2006-09-28 : 05:03:14

I want to retrieve records upto 10 i.e
Records must be less than or equal to 10 but not greatrer than 10
(its not hard and not my question

)
The problem is that I want to retrieve (these 10 records) randomly. i.e it is not predecided which records must be returned.I want a query which retrieves records on runtime. Is it possible with SQL Server 2005 ...If yes then kindly tell me the query or any idea or link...

Azhar Rahi
Software Engineer
Eye4tech Pvt Ltd,Lahore
Pakistan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-28 : 05:07:34

It is possible with SQL Server 2000 too!

Use ORDER BY NEWID()

select * from yourtable
where pkcol in (select top 10 pkcol from yourtable where ... order by ...)
order by newid()

But it will be slow.

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-09-28 : 05:30:14

This comes up now and again of course ...

... I wonder if, for better performance, you happened to have a GUID in the record already (e.g. for Replication) whether something like

select TOP 10 * from yourtable where MyGUIDColumn > newid()

might do the trick.

Indeed, for an Identity it might be sufficiently "random" to do

SELECT @intStart = RAND(1) * MAX(MyID) FROM MyTable
SELECT TOP 10 * FROM MyTable WHERE MyID > @intStart

given that this type of thing is usually for things where the randomness is not crucial - I mean, allocating lottery numbers this way would, mathematically, be a bad idea, but ...

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-28 : 09:00:37

quote:
Originally posted by Kristen
...
SELECT @intStart = RAND(1) * MAX(MyID) FROM MyTable
SELECT TOP 10 * FROM MyTable WHERE MyID > @intStart
...

You have a possibility of getting less than 10 rows with this. This will prevent that, as long as you have no missing IDs in the sequence.

SELECT @intStart = RAND(1) * MAX(MyID)-10 FROM MyTable

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2006-09-28 : 09:28:30

Good thinking.

I'm surprised you didn't comment on how un-random it was, but maybe you think it would have a use?

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-28 : 10:13:14

I didn't comment about it not being random because I think you made it clear you were only going for a random start point.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources