MySQL Query Help

[suarve]

Hi,

Can't figure out how to do the following

I have a database as follows:

id
email
num_tickets

A couple of random records would look like:

50, [email protected], 4
80, [email protected], 1
85, [email protected], 3

This table has approx 14,000 rows all with a value of num_tickets from 1-4.

What I need to do is select a list of random winners as there are 10,000 tickets available - so I'm not just selecting 10,000 rows from the database.

Does anyone have an idea for this, as I'm stumpted

Thanks


EDIT: just to clarify, I kind of want to select random rows until the sum of num_tickets reaches 10,000.

EDIT EDIT: Think I'm onto soemthing here:

SELECT @i:=0;
SELECT id, num_tickets, @i:=@i+num_tickets AS i FROM main_entrant LIMIT 10000;

Have got the following working, just struggling to limit the sum of my var amd select random records:

SELECT @sumTickets:=0;
SELECT id, num_tickets, @sumTickets:=@sumTickets + num_tickets AS sumTickets FROM main_entrant LIMIT 100

 

[GravyMonster]

So you need to select X number of records where num_tickets totals up to 10,000?

Is that correct?

 

[suarve]

So you need to select X number of records where num_tickets totals up to 10,000?

Is that correct?

Yes

I think I'm onto soemthing, see my second edit

 

[NathanT]

You will have to write some additional logic to select random records. Just using an ORDER by RAND() will take an age with 14000 rows of info looping a possible 10000 records. Not even sure if it will work using the loop that you propose.

Does it have to be done in pure SQL or can you maybe knock up a quick PHP script or something similar, would certainly make it a lot more customisable and easier to optimise?

 

[kwerk]

Something like this (probly wrong somehow but you get the idea):


$query = "SELECT * FROM table";
$rows = mysql_num_rows($query);

// number of lucky winners
$winners = 10;

for($count = 1; $count <= $winners; $count++) {
$winner = rand(0, $rows);
$query2 = "select * from table LIMIT 1 OFFSET $winner";
$randomwinner = mysql_fetch_assoc($query2);
extract($randomwinner);
echo "Random winner". $count .": ". $email;
}

 

[suarve]

You will have to write some additional logic to select random records. Just using an ORDER by RAND() will take an age with 14000 rows of info looping a possible 10000 records. Not even sure if it will work using the loop that you propose.

Does it have to be done in pure SQL or can you maybe knock up a quick PHP script or something similar, would certainly make it a lot more customisable and easier to optimise?

Was just thinking that this morning. May have a go in PHP.