Random select

[Wim Roffil]

Hi,

When I do a select with a limit I get always the same records. Usually this
is the desidered effect.

But now I want to do a random select. So every time I do a select I should
get a different set of results.

What is the best way to achieve this effect?

Thanks,
Wim

[Randell D.]

Wim Roffil wrote:

Hi,

When I do a select with a limit I get always the same records. Usually this
is the desidered effect.

But now I want to do a random select. So every time I do a select I should
get a different set of results.

What is the best way to achieve this effect?

Thanks,
Wim

What is your problem? Do you really want random records, or are you
bothered that you get the same records everytime you perform a select?

If its the latter, you should use a WHERE clause in your SELECT and
perhaps also a LIMIT start,rows

Thus,something like

SELECT firstname,lastname FROM myTable WHERE ID>10 LIMIT 5,12

will find all records that have ID>10 and return 12 records, starting
from the 5th one found (thus something like records with ID 15 to 27).

If you want to read random records, request a random record (I'm
assuming you've got a column/cell that is AUTO_INCREMENT that you could
use as reference).

Does that help you any?
randelld

[Wim Roffil]

"Randell D." <re******************************@fiprojects.moc > schreef in
bericht news:LpHQd.407506$8l.401215@pd7tw1no...

Wim Roffil wrote:

Hi,

When I do a select with a limit I get always the same records. Usually this is the desidered effect.

But now I want to do a random select. So every time I do a select I should get a different set of results.

What is the best way to achieve this effect?

Thanks,
Wim
What is your problem? Do you really want random records, or are you
bothered that you get the same records everytime you perform a select?

Thank you for your reply.

I need reaaly random records. From a big collection I want to show the
visitors of my webpage just 5 or 10. Because adjacent records may be related
they should be spread over the whole collection. AUTO_INCREMENT is not an
option: it is not already there and the query is not over the whole table
but over a part of it (there is a WHERE clause).

If its the latter, you should use a WHERE clause in your SELECT and
perhaps also a LIMIT start,rows

Thus,something like

SELECT firstname,lastname FROM myTable WHERE ID>10 LIMIT 5,12

will find all records that have ID>10 and return 12 records, starting
from the 5th one found (thus something like records with ID 15 to 27).

If you want to read random records, request a random record (I'm
assuming you've got a column/cell that is AUTO_INCREMENT that you could
use as reference).

Does that help you any?
randelld

[d]

try
select * from table order by rand() limit 0,5

This doesn't have the best performance for large recordset, but is ok
for small tables
--
Jay
Wim Roffil wrote:

"Randell D." <re******************************@fiprojects.moc > schreef in bericht news:LpHQd.407506$8l.401215@pd7tw1no...

Wim Roffil wrote:

Hi,

When I do a select with a limit I get always the same records.

Usually

this is the desidered effect.

But now I want to do a random select. So every time I do a select

I

should get a different set of results.

What is the best way to achieve this effect?

Thanks,
Wim
What is your problem? Do you really want random records, or are you
bothered that you get the same records everytime you perform a select?
Thank you for your reply.

I need reaaly random records. From a big collection I want to show the visitors of my webpage just 5 or 10. Because adjacent records may be related they should be spread over the whole collection. AUTO_INCREMENT is not an option: it is not already there and the query is not over the whole table but over a part of it (there is a WHERE clause).

If its the latter, you should use a WHERE clause in your SELECT and
perhaps also a LIMIT start,rows

Thus,something like

SELECT firstname,lastname FROM myTable WHERE ID>10 LIMIT 5,12

will find all records that have ID>10 and return 12 records,

starting from the 5th one found (thus something like records with ID 15 to 27).
If you want to read random records, request a random record (I'm
assuming you've got a column/cell that is AUTO_INCREMENT that you could use as reference).

Does that help you any?
randelld