efficiently randomize results - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

efficiently randomize results

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-11-10 : 16:41:56

Hi,

I have a very simple query of 10 records that I am trying to randomize. I am weighing randomizing the order in the front end (asp.net) vs doing it in SQL server.

Does anyone recommend a good way to randomize the order of 10 records in SQL ? Trying to figure out the most efficient way.

Thanks once again!
Mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-10 : 16:44:40

ORDER BY NEWID()

E 12°55'05.63"
N 56°04'39.26"

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-11-10 : 16:46:06

Order by NewID() is fairly popular

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-11-10 : 16:47:27

I think this is the 4th time in 3 days where I lost the posting race to peso... I want him tested for steroids! :)

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-10 : 17:15:09

quote:
Originally posted by Vinnie881

I think this is the 4th time in 3 days where I lost the posting race to peso... I want him tested for steroids! :)

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Vinnie,

What about visakh? Which test?

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-11-10 : 17:20:50

Hey Guys,

Your feedbacks much appreciated ... and yes agreed Peso is very tough to beat ! I got the newID() working on a simple query, but wondering exactly how I would form a query where I have multiple ORDER BY's . Do I need a temp table ?

For example my query could be

select top 10 * from tbluserdetails order by userID

I would want to randomize these results using NEWID()

How exactly should I form this for the most efficient way possible ?

Thanks once again!
Mike123

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-11-10 : 17:46:48

SELECT *
FROM
(
select top 10 * from tbluserdetails order by userID
) AS T
ORDER BY NEWID()

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-10 : 17:47:49

[code]select top 10 * from tblUserDetails order by NEWID(), USERID[/code]
edit: i don't read. i thought you just want 10 random IDs... ignore this.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-11-11 : 06:09:52

awesome guys thanks very much!