[2005] how to select Random Row each time , Very Challenging

Dear all,

i have a database full of Tips and Tricks.

each subject in my database has one or more related rows in the tips tbl.

on my application startup i need to show a random tip each time.

if i have a resultset of 8 rows for a defined subject, how can i make my application randomely choose one row to display.

The solution must retrieve the data of one row only , because it is a web application so it will be very slow to retrieve the whole tips rows in order to select one.

also i need it as i mentioned before to display random tip each time it starts.


just like the Visual studio AD Rotator but in access of database and not different files.

this is challenging, isn't it ?? ;)

inlight me Experts ....


thx in advance

Everything stored in a database? What database are you using?

-tg

On startup you display a random tip, but you have many subjects with many tips per subject. Are you willing to show any tip from any subject?

Are you able to add a new field, and do you have an autonumber style field?

The simplest case would be if you had tips 1 through N and they are numbered as such in a single field, with no blanks. Get N by obtaining the MAX(number), then choose a random number, then do a query on that line (it could return just the tip, a VERY fast and simple query).

However, if you want to choose a random one from a set that is not continuous numbers (either a subset of all the tips, or a whole table with non-continuous indexes), it seems like you might want to populate an array with the indexes that are available, then choose randomly within that array. This is slower, because populating the array means reading the index field for all the results, before getting the tip. I don't know enough about the constraints on a web-based prog to know whether this is feasible or not.

I was thinking of something simpler....

Code:

---

SELECT Top 1 *
FROM tblTips
ORDER BY NEWID()

---

But that only works in SQL Server... I'm sure there's an Access equivelent, but danged if I know what it is.

-tg

I was also wondering about this:

I seem to remember that you could restrict the number of records returned by a read. Would it be possible to do this:

Say that there are 15 records. Select a random number from 1 to 15. For argument, lets say the record needed was 7.

Set a query that returns 6 records per cluster. Discard the first cluster, and use the first line of the second cluster.

Of course, if I misunderstood this feature (that I only glanced at a year ago, so I could easily be wrong), then this is all crappola.

I would think that this would be pretty easy. Give your records sequential IDs, get the number of records, generate a random number in the range of the IDs, get the record with that ID. If you want to have the tips grouped into sections than have a two-column primary key. Have a section ID and a tip ID. Get the number of sections and the number of tips in each section, generate a random number in the range of the section IDs, generate a random number in the range of the tip IDs for that section, get the record with those IDs.

first of all thank you for your answers

second: i am using sql server

third : this is a web project, so each of my pages should display tips relative to the subject of the page ( which is already fixed and known)


Fourth: what did u mean by NEWID() TechnoGum , is it a function or a defined column name. if it is a column name it will not work as it will always return the same row - and that what i dont want to do.

finally with jmcilhinney, to give all my rows sequential ids and select them , this will requires me to do multiple reads on the database. and that is what i am trying to avoid. this will slow the page very much as it will wait for the sql server queries to execute.

thx all for your time

I think you have misunderstood my suggestion. You would only need to perform a single query to get the total number of records or the number of records per section. Then you just need to perform a single query to get your random tip.

and what if i only have the Identity column which is not going 1 2 3 4 5 6

but it goes without an order 1 5 8 10

how can i manage this situation, i dont have IDs Column for each subject moreover it will be a headache to reserialize them whenever the user decides to delete a row in the middle . . .

NEWID() is a SQL Server function.... it returns a GUID each time it is called.... when used in the manner I suggested, it causes a "randomness" to the selection. If you also want it relevant to the page, then also include a where clause to select only those that are relevant.

-tg

i will give it a try technogum ,

i hope it will work fine

thx for your advise :)

I have heared somewhere that a GUID can be slow, but there's nothing like testing.

Can anybody confirm/refute that odd memory I had about you being able to set the number of rows returned? If that is possible, the technique I mentioned would work regardless of the indexing of the rows, or the number of rows.

Select a random row with MySQL:

Code:

---

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

---

Select a random row with PostgreSQL:

Code:

---

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

---

Select a random row with Microsoft SQL Server:

Code:

---

SELECT TOP 1 column FROM table
ORDER BY NEWID()

---

Select a random row with IBM DB2

Code:

---

SELECT column FROM table
ORDER BY RAND()

---

Select a random record with Oracle:

Code:

---

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

---

if all these answers are valid and true then i must mark this thread resolved


thx very much for this complete and typical answer TokersBall_CDXX :)