Selecting Rows of data randomly...

[shahab]

Hi ,all

How can i select 20 records of a table Randomly using the Id Column? :rolleyes:

[TechnoTone]

There are many ways you could do this, each with different merits depending on the situation.

 

You could write a routine that pulls back all the records into an array and then picks out 20 random items. This would not be a very good solution if there were a lot of records to choose from. It could be improved by first getting just the ID column and then, once you've picked you random 20 items, get the rest of the data.

 

Another way could be to add another column to the table with a floating point data type. Then, you could run an update query that would set this new column to a random number for each record. Finally, you could select the top 20 records, sorting by this random number column. Again, this may take a while if you have lots of records in the table.

 

There are probably other ways that I haven't considered but the point is - the best solution is dependent upon your situation. Are you using a database server that could do some of the hard work for you? How many records are there? Is there any data in the table that could help you to randomize the selection and reduce the number of records that you pull back?

[Celeron]

If I were doing that, I would generate some random numbers beforehand with Random Number class and then call all rows with ID's of those random numbers.

[shahab]

Thanks techno,My Table is this:

CREATE TABLE [dbo].[tblQBank] (
[QID] [int] IDENTITY (1, 1) NOT NULL ,
[CatId] [int] NOT NULL ,
[QLevel] [tinyint] NOT NULL ,
[Question] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DescAnswer] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Answer] [tinyint] NULL 
) ON [PRIMARY]
GO

 

CREATE TABLE [dbo].[tblQCategory] (
[CatId] [int] IDENTITY (1, 1) NOT NULL ,
[major] [nchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[lesson] [nchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[topic] [nchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]

This is really a huge table with at last 400,000 record(Predictable)!this is a questionDatabase with answer ,... using for online tests!!

 

*Would u please show me a code about those two ways u talked about:)

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

Dear celeron

with Random Number class and then call all rows with ID's of those random number

would u please show a code i am not familiar enough with this class:)

[Nerseus]

If you know how to query the table, you're almost done... if you don't, then ask how to write the code to query a table.

 

If you can guarantee that the IDs in your table are 100% in order with no gaps, then you can use pure client side code to randomly pick 20 numbers (make sure you don't have duplicates). Then comma separate those numbers and build up a SQL string like this:

"SELECT * FROM tblQBank WHERE QID IN (" + ids + ")"

 

So if ids is a string that looks like "1, 56, 1054, 8763, 456, 23" then the SQL becomes:

SELECT * FROM tblQBank WHERE QID IN (1, 56, 1054, 8763, 456, 23)

 

If you can't guarantee the ID column will be sequential with NO gaps, then you'll need another approach. Here's a nice one (pseudo sql server code):

CREATE TABLE #IDList ([id] int)

SELECT qID INTO #AllIDs FROM tblQBank
SET @i = 0
WHILE @i < 20
BEGIN
   -- Change the 0 below to use something more random
   SET @rnd = RAND(0)

   SELECT @Value = at position @rnd FROM #AllIDs

   -- Check if @Value exists in #IDList

   IF <doesn't exist>
   BEGIN
       -- Insert @Value
       INSERT INTO #IDList SELECT @Value
       SET @i = @i + 1
   END
END

 

-Nerseus

[Arch4ngel]

Like a wise monk said...

It's always better to centralize the code on the server so it's easier understandable, updatable and require less coffee for programmers.

 

By doing this on the server... you also prevent some form of hacking through your web page. It's kinda more secure.

 

Don't forget be Zen.

[shahab]

Very Great ,thanks Dear Nerseus:)

[shahab]

SELECT @Value = at position @rnd FROM #AllIDs

Would u please explain it?

[Arch4ngel]

I don't understand either where he was going... but If you use a CURSOR with FETCH NEXT until you reach the right row... you should get a good result.

 

(What's the meaning of this line ?????????)

SELECT @Value = at position @rnd FROM #AllIDs

[shahab]

I had no experiene in using cursors and T-Sql like that,Does anyone say about Nerseus

Program in a simle way?!

[Joe Mamma]

the answer

 

Hi ,all

How can i select 20 records of a table Randomly using the Id Column? :rolleyes:

 

SQL server???

simple

 

forget about your id column

and order by an inline call to newId()

 

select top 20 from mytable order by newID()

[georgepatotk]

select top 20 from mytable order by newID()

Such a good idea..

[shahab]

SELECT TOP 10 * FROM tblQBank ORDER BY NEWID()

Now just another problem....

How can I add some conditions to this selection:

 

SELECT TOP 10 * FROM tblName ORDER BY NEWID()

where field_name <> 0

 

Error msg:

Server: Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'where'.

[PlausiblyDamp]

SELECT TOP 10 * FROM tblName 
where field_name <> 0
ORDER BY NEWID()

[shahab]

Thanks alot...