Select a number of rows randomly in a table ?

[Phan Tien]

How can I select a number of rows randomly in a table in Microsoft Access or
SQL Server? I tried with many ways but I can't solve this problem Please
help me.
Thank you very much

 

[Robin Tucker]

In SQL server (this is not really VB related), I use this stored procedure
to return a random record:

ALTER PROCEDURE dbo.proc_TEST_Get_Random_Record
AS
BEGIN

DECLARE @ID INTEGER
DECLARE @rand_num INTEGER
DECLARE @num_recs INTEGER

SET NOCOUNT ON

/*
Work out size of table
*/

SELECT @num_recs = COUNT(*) FROM theTable

/*
Choose a random number to the extents of the table
*/

SET @rand_num = ROUND ( @num_recs * RAND () + 1, 0, 1 )

/*
Use a cursor to access the random record
*/

DECLARE Records CURSOR SCROLL static read_only FOR
SELECT ID FROM theTable
OPEN Records
FETCH ABSOLUTE @rand_num FROM Records INTO @ID
CLOSE Records
DEALLOCATE Records

/*
Select the record data.
*/

RETURN @ID
END