Random Function - 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

General SQL Server Forums

New to SQL Server Programming

Random Function

Author

Topic

spinoza
Starting Member

49 Posts

Posted - 2006-01-25 : 09:02:32

Hi all,

I have a table with a multiple fields. I want to create a query that it will show me one dataset according to a random numder the system choose...

For istanse if it choose the number 13 it will show all the records conserning that id

I used the following query, but of course it is wrong

select * from famous_queries
where id = rand(id)

Could someone help me with this please?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-25 : 09:07:14

select * from famous_queries
where id = @id order by newid()

Madhivanan

Failing to plan is Planning to fail

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-01-25 : 09:07:38

From Books OnLine:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
RAND
Returns a random float value from 0 through 1.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

This means that you have to multiply rand() by (the max id in your table converted to a float number) and the result of this must be rounded off to an integer number to work accurately.

otherwise - just rand() * max_ID

Duane.

spinoza
Starting Member

49 Posts

Posted - 2006-01-25 : 09:09:21

I am amased...More quickly than your shadows....
Big big thanks

spinoza
Starting Member

49 Posts

Posted - 2006-01-25 : 09:11:50

Must declare the variable '@id'.

I received the above error when i use the
select * from famous_queries
where id = @id order by newid()

what i have to do here?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-25 : 09:13:08

Declare @id int
set @id=13
select * from famous_queries
where id = @id order by newid()

Madhivanan

Failing to plan is Planning to fail

spinoza
Starting Member

49 Posts

Posted - 2006-01-25 : 09:15:51

ok...it works...but i suppose i wasnt clear in my query... I would like the system randomly to choose a number...is it possible to do that???

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-25 : 09:20:14

select Top 1 * from famous_queries
order by newid()

or

select * from famous_queries
order by newid()

Madhivanan

Failing to plan is Planning to fail

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2006-01-25 : 09:20:59

See if this helps: http://snipurl.com/lx6j

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt? http://www.insidesql.de/blogs

spinoza
Starting Member

49 Posts

Posted - 2006-01-25 : 09:22:48

sorry for being irritating, but the above query returns all the fields. Could i limited to one only???

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-25 : 09:30:29

Did you try this?

select Top 1 * from famous_queries
order by newid()

Madhivanan

Failing to plan is Planning to fail

spinoza
Starting Member

49 Posts

Posted - 2006-01-25 : 09:33:55

Thank you... thank you.... thank you sir...

Muchas Gracias

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-25 : 09:35:53

quote:
Originally posted by spinoza

Thank you... thank you.... thank you sir...

Muchas Gracias

Well. You must have read my third reply clearly

Madhivanan

Failing to plan is Planning to fail

spinoza
Starting Member

49 Posts

Posted - 2006-01-25 : 09:40:36

and i wear glasses... I think i have to visit my doctor soon again...

Anup Shah
Starting Member

14 Posts

Posted - 2006-01-26 : 00:57:23

hy

I think newid() is not inbuilt function.
so can u tell me what is newid() function.

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2006-01-26 : 03:13:39

Check out the explanations for NEWID in BOL.

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt? http://www.insidesql.de/blogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources