Weighted random SQL query

Help!

Is something like this correct?

SELECT id, uri, title 
FROM `links`
WHERE active = "1" AND mainweight != 0 
ORDER BY Rand()*(1/mainweight) LIMIT 10

you have to order by a column name... I'm not entirely sure what you are attempting there. I'd probably need some data as well to see.

Actually you don't I've just done some tests and the above query works exactly as I want. I suppose I was just posting here as a sanity check. Now I can give my results weightings :D Yipeee..

I assume you are trying to access a database randomly, I would process the mainweight randmom number then look for it... so, I'd order by whatever you want and WHERE mainweight = some var that contains the mainweight I calculated I need.

Well I want to return any number of results at random but some must be more random than others; man I sound like George Orwell.

Anyway it works.

Im sure I recently saw an example of selecting some random rows in mysql and it didnt order by a row but did pretty much what Indiepath is doing. So.. if its MySql then it looks OK I think in terms of syntax.

SELECT col1, col2 from table1 where col3='x' order by Rand() LIMIT 1

So.. Rand() is a float between 0 and 1. Mainweight is ??... an int ?.. and is it the smaller the mainweight the more likely you want it to be selected ?

If yes then it looks like it should do what you want.

Mainweight can be anything as long as the scheme is used throughout. For example it could be the number of clicks a link has got, the items will greater clicks bubble to the top.

So MySql is doing the hard program execution part for you. what's next, SQL-Invaders hu?