random query

Question

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

anbu SSC-Addicted Points: 407 More actions · Answer 1

create table test1 (filed1 int identity, field2 int)

insert into test1(field2) values(1002);

insert into test1(field2) values(1003);

select * from test1

Hope this helps.

Thanks and regards,

Anbu

smfiber SSC Veteran Points: 265 More actions · Answer 2

Anbu,

Thanks, I figured this one out using cold fusion and RANDRANGE.

Thanks,

Andrew

Andrew
http://eshopsoho.com

DavidT SSCommitted Points: 1727 More actions · Answer 3

Alternative:

declare @a int

declare @b-2 int

set @a = (select Max(Col_Id) from Table)

set @b-2 = (select cast(rand()*@a as int))

select * from table where col_id=@b

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 4

declare @i int

select @i = count(*) * rand() + 1 from mytable

select @i

select top 1 *

from myTable mt1

where @i = (select count(*) from myTable mt2 where mt2.col_id <= mt1.col_id)

This allows for non sequential numbers in col_id

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 5

A different approach using NEWID() is shown here (http://www.sql.nu/read.asp?id=4), and it works nice if you have a small table (like with 10 rows).

--

Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

el.c. Old Hand Points: 376 More actions · Answer 6

Hi

You can try

SELECT TOP 1 NEWID(), * FROM yourTble ORDER BY 1

hth

el.c.

myLittleTools.net :: web-based applications

myLittleTools.net :: web-based applications
http://www.mylittletools.net