SQL-->random selections 
Author Message
 SQL-->random selections

Hello all...

Is there any way to randomly select one row from a database table with MS SQLServer?

This is necessary for a system I'm working on right now and my books lead me to believe there is
no such functionality in SQL period.

Please let me know if this is untrue.

Thanks in advance,,,

HS


Impact Computer Systems



Thu, 04 Nov 1999 03:00:00 GMT
 SQL-->random selections



Quote:
>Hello all...

>Is there any way to randomly select one row from a database table with MS SQLServer?

>This is necessary for a system I'm working on right now and my books lead me to believe there is
>no such functionality in SQL period.

Returning random data is pretty much exactly the opposite of what SQL
Server was designed for.

It has no native capability for doing what you want.

Terry

--------------------

Terry Carmen
Meadowbrook Software

NOTE TO SPAMMERS:
BAD THINGS will happen to any site that sends me email spam.



Fri, 05 Nov 1999 03:00:00 GMT
 SQL-->random selections

Quote:

>Is there any way to randomly select one row from a database table with MS SQLServer?
>This is necessary for a system I'm working on right now and my books lead me to believe there is
>no such functionality in SQL period.

        Only one method occurs to me (and it's got a few major problems):

1) Create an identity column on the table.

min(identity)) to get your range.

number.
4) Verify the row number exists, if not generate a new one.
5) Select where identity = row number.

This can conceivably loop indefinitely (especially if there are large gaps
in the identity numbers), it requires maintenance of the identity column,
and the overhead is a bit large for a simple select statement.

Jonathan Conway



Fri, 05 Nov 1999 03:00:00 GMT
 SQL-->random selections


Quote:
>I can think of an easier way, though maybe not as fast:
>1)  use select count(*) to get the count of records in the table
>2)  use rand on the count to specify a record
>3)  use a cursor to select to the number found in #2

Here's an improvment: replace step 3 with



   FROM   table

   SET ROWCOUNT 0

The variables will hold the values of the last row affected by the SELECT,
and thus randomly chosen. The advantage is that you get the overhead in-
volved with cursors.


6.0 you can use EXEC to get the same effect.

--

F=F6r =F6vrigt anser jag att QP b=F6r f=F6rst=F6ras.
B=65sid=65s, I think QP should b=65 d=65stroy=65d.



Wed, 10 Nov 1999 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Random Selection in SQL 2000

2. SQL random selection

3. Random selection of records in PL/SQL

4. Random selection of rows in a query

5. Random selection from table

6. "Random" selection

7. Selection of Random Records

8. Random selection of numbers

9. Random Row Selection

10. random selection

11. Random selection from table

12. SQL 7.0 Random function, does not work like SQL 6.5 Random function


 
Powered by phpBB® Forum Software