Select Random Numbers with Limit 50

[danasegarane]

Hi all,
I am having one table with id from 1 to 50. Now I want to return random records from that table using rnd.The point is that is there any funntion like

Select [id] from tblids where id not in (values) with rnd

Thanks in Advance
Dana

 

[ajetrumpet]

Dana,

You can't do it with "Rnd" alone, but you can do it with this...

SELECT *
FROM table
WHERE [ID] = Int((50 * Rnd) + 1);

1 = lowerbound of range
50 = number of integers greater than "1" to extend the range

In other words, if you had this...

WHERE [ID] = Int((4 * Rnd) + 12)

you would get random records back with an ID in range "12-15"

 

[danasegarane]

Dear aje,
Thanks for ur reply.It worked well. I have a ids of 1-50. I want to retrive the ids from 1-50 in random
order.If all the IDs are returned from the id column,then query should return a null values.What logic can I use.
Shall I store the previously returned values in another field and compare the query result

Thanks in Advance
Dana

 

[ajetrumpet]

If all the IDs are returned from the id column,then query should return a null values. Shall I store the previously returned values in another field and compare the query result

I'm not sure what you're trying to do Dana...the question is a bit vague. The formula above returns random records, not all of them. It only specifies a RANGE in which to look for the random record.

If your ultimate goal is to return all of your records in a random order, I'm not sure you can do it. I know you can set autonumber to assign randoms as you enter data, but I don't think you can get it to assign randoms by appending existing records to a new table (having no records) with a brand new "random" autonumber in it (that is, if you're even using the autonumber feature for your ID's)

By the way, I don't like autonumber.

 

[The_Doc_Man]

ajetrumpet, autonumber has its proper place when a more meaningful key is not possible due to issues of uniqueness.

But to the problem at hand...

To randomize the order of appearance of 50 records, you must work very hard.

The simplest way to do this is to include a SINGLE or DOUBLE in the table as your randomizer value. Then you CAN write an UPDATE query that sets this field to the value of the next call to the rnd function.

Then instead of sorting on the PK of the table, sort on the random key. The odds of getting a duplicate from this method are quite low. (Not impossible, though.) You have a couple of other choices, but this is a good bet most of the time.

 

[ajetrumpet]

Thanks Doc, I'll try that.

I don't know if I follow you, but I am a bit tired right now.

 

[danasegarane]

To randomize the order of appearance of 50 records, you must work very hard.
.

Yes I want to retrive all the 50 records in a random order.If all the 50 records are retrived then i want to retrive a null record.Is it possible ?

 

[The_Doc_Man]

?

Now there is something you don't want to see - a null record.

If you are using SELECT to find this record, you are thinking wrongly. Because in SQL, null records don't exist to be located. (If they do, you have screwed up so badly that we can quit right now...)

In VBA, using a query as the basis for a recordset, you would know you had reached the end of the set by testing for the recordset-name.EOF property to be TRUE.

But in SQL, it doesn't happen quite this way. I'm going to read between the lines.

You want random retrieval of records. It sounds like you are dealing with them one at a time AND that you don't want to see them again once they have been processed.

In that case, you need to explain your process a bit more before any of us can advise you better. Because it SOUNDS to me that you are making invalid assumptions in the way that Access - and SQL - works. Either that, or you are explaining your goals poorly. (No insult intended...)

 

[odin1701]

int generateRandomNumber()

{

return 5;  //chosen by fair dice roll.
           //guaranteed to be random.

}

 

[danasegarane]

Dear The_Doc_Man ,
Thanks for ur reply. I have twotable with the following structure
tblrecords
1.studentid-number
2.Question_ attended-Text

and
tblquestions
1.question id-number
2.questions-Text

Now What i am doing is select a random questionid and question from tblquestion and show to the students(Max questions).If the students answers that question than that answer wil be stored in another table.And in the uestion_ attended column of the tblrecords table will be appended with just answered question with a semicolumn separted.

What I want to do now is, If the student answers all the question, Then I have to give him a message.I am using Asp as my front end. Am I clear now ?

Dana

 

[The_Doc_Man]

When you said ASP you knocked me out of the box. I don't use that feature.

 

[danasegarane]

No problem,
Give me Idea in VBA Excel. I wll convert that into ASP

 

[unclejoe]

Hi Dana,

If you want to select all records at random...

SELECT Rnd([ID]) AS Q1
FROM tblQuestions
ORDER BY Rnd([ID]);

Hi all,
I am having one table with id from 1 to 50. Now I want to return random records from that table using rnd.The point is that is there any funntion like

Select [id] from tblids where id not in (values) with rnd

Thanks in Advance
Dana

 

[pdx_man]

What comes to mind is the old question of shuffling and dealing cards. How does a computer shuffle virtual cards? Well, one solution, and what would work here (I believe if I am understanding the jist of this) is instead of dealing from the top of the deck, just pull a card out at random from an ordered deck. Same thing here. My psuedo code:

Copy table to a recordset (rs)
Do 
   Generate random number from 1 to number of records (first time 50, next 49 ...) Int((rs.recordcount * Rnd) + 1)
    Insert into destination recordset (rd) the randomly generated record number with a number designating the order in which it was generated
    Delete record from (rs)
until out of records (rs)

Make sense?

 

[danasegarane]

Thanks Pdx,
It sounds something different.I wll let it have a go

Dana