Getting random record from database with group by

Question

Hello i have a question on picking random entries from a database. I have 4 tables, products, bids and autobids, and users.

Products
-------  
id 20,21,22,23,24(prime_key)
price...........
etc...........

users  
-------
id(prim_key)  
name user1,user2,user3  
etc  

bids  
-------
product_id  
user_id  
created  

autobids  
--------
user_id   
product_id 

Now a multiple users can have an autobid on an product. So for the next bidder I want to select a random user from the autobid table

example of the query in language:

for each product in the autobid table I want a random user, which is not the last bidder.

On product 20 has user1,user2,user3 an autobidding.
On product 21 has user1,user2,user3 an autobidding

Then I want a resultset that looks for example like this

20 – user2
21 – user3

Just a random user. I tried miximg the GOUP BY (product_id) and making it RAND(), but I just can't get the right values from it. Now I am getting a random user, but all the values that go with it don't match.

Can someone please help me construct this query, I am using php and mysql

Answer 1

You can use the LIMIT statement in conjunction with server-side PREPARE.

Here is an example that selects a random row from the table mysql.help_category:

select @choice:= (rand() * count(*)) from mysql.help_category;
prepare rand_msg from 'select * from mysql.help_category limit ?,1';
execute rand_msg using @choice;
deallocate prepare rand_msg;

This will need refining to prevent @choice becoming zero, but the general idea works.

Alternatively, your application can construct the count itself by running the first select, and constructing the second select with a hard-coded limit value:

select count(*) from mysql.help_category;
# application then calculates limit value and constructs the select statement:   
select * from mysql.help_category limit 5,1;

Answer 2

The first part of the solution is concerned with identifying the latest bid for each product: these eventually wind up in temporary table "latest_bid".

Then, we assign randon rank values to each autobid for each product - excluding the latest bid for each product. We then choose the highest rank value for each product, and then output the user_id and product_id of the autobids with those highest rank values.

create temporary table lastbids (product_id int not null, 
                                 created datetime not null, 
                                 primary key( product_id, created ) );

insert into lastbids 
select product_id, max(created)
from bids
group by product_id;

create temporary table latest_bid ( user_id int not null, 
                                    product_id int not null, 
                                    primary key( user_id, product_id) );

insert into latest_bid
select product_id, user_id 
from bids b
join lastbids lb on lb.product_id = b.product_id and lb.created = b.created;

create temporary table rank ( user_id int not null, 
                              product_id int not null, 
                              rank float not null, 
                              primary key( product_id, rank ));

# "ignore" duplicates - it should not matter
# left join on latest_bid to exclude latest_bid for each product

insert ignore into rank 
select user_id, product_id, rand() 
from autobids a
left join latest_bid lb on a.user_id = lb.user_id and a.product_id = lb.product_id 
where lb.user_id is null;

create temporary table choice 
as select product_id,max(rank) choice 
   from rank group by product_id;

select user_id, res.product_id from rank res
join choice on res.product_id = choice.product_id and res.rank = choice.choice;