choosing random record from a table

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

.NET Inside SQL Server (2005)

choosing random record from a table

Author

Topic

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2009-07-17 : 06:04:12

Hi
There is a table Composed of an identity field(ID) and others.
I wanna select 3 random records from this table and show them in a c# application. to do so, firstly I have to figure out how many records exist in table. I can do this with select count(*) statement. For example, the result should be something like 11,23,6. Now I should select the 11th,23rd,6th records from the table. I can do these operations separately in two commands in the c# application. I mean to find out the numbers through a command and then choose 3 random numbers in the range in the C# application and then select those records from the table. But I want do all these done with one command and in the sql server itself. I mean the random numbers be selected in the sqlserver itself.
Thanks to every body.

****<< I Love MTN.SH >>****

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-17 : 06:32:00

Try this

DECLARE @maxRandomValue TINYINT = 100 , @minRandomValue TINYINT = 0

SELECT CAST(((@maxRandomValue + 1) - @minRandomValue)
* RAND() + @minRandomValue AS TINYINT) AS 'randomNumber'

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-17 : 06:35:47

quote:
Originally posted by senthil_nagore
DECLARE @maxRandomValue TINYINT = 100 , @minRandomValue TINYINT = 0

we cannot assign a default value to a local variable..right

-------------------------
R..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-17 : 06:53:43

[code]
select top 3 *
from yourtable
order by newid()
[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-17 : 07:17:10

quote:
Originally posted by rajdaksha

quote:
Originally posted by senthil_nagore
DECLARE @maxRandomValue TINYINT = 100 , @minRandomValue TINYINT = 0

we cannot assign a default value to a local variable..right

-------------------------
R..

Except you are on SQL Server 2008

Madhivanan

Failing to plan is Planning to fail

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-17 : 07:25:52

Hi

yes i have 2005 box...

-------------------------
R..

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2009-07-17 : 08:30:11

i have 2005 box too.
what i have to do?

****<< I Love MTN.SH >>****

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-17 : 08:43:33