Select random rows up to a limit

Question

SELECT *
FROM my_players
WHERE player_type = 'IF'
ORDER BY cardweight / rand() DESC limit 3

That is my current query, What I am trying to do is select random rows for which I have the * in my query, so I am trying to get the psbin (which is a column in my table) it is a int and I want the psbin of all these rows combined not to go over 250k.

Create table statement is:

CREATE TABLE `my_players` ( 
`myplayer_id` int(11) NOT NULL AUTO_INCREMENT, 
`player_id` int(11) NOT NULL, 
`First_Name` varchar(255) NOT NULL, 
`Last_Name` varchar(255) NOT NULL, 
`Rare` varchar(255) NOT NULL, 
`Foot` varchar(255) NOT NULL, 
`Height` varchar(255) NOT NULL, 
`Date_Of_Birth` varchar(255) NOT NULL, 
`player_name` varchar(255) NOT NULL, 
`player_nation` int(11) NOT NULL, 
`player_club` int(11) NOT NULL, 
`player_league` int(11) NOT NULL, 
`player_type` varchar(255) NOT NULL DEFAULT 'GOLD', 
`player_pos` varchar(255) NOT NULL, 
`totwImage` varchar(255) DEFAULT NULL, 
`psbin` int(11) NOT NULL, 
`xboxbin` int(11) NOT NULL, 
`ps3bin` int(11) NOT NULL, 
`360bin` int(11) NOT NULL, 
`xmin` int(11) NOT NULL, 
`xmax` int(11) NOT NULL, 
`pmin` int(11) NOT NULL, 
`pmax` int(11) NOT NULL, 
`cardweight` int(11) NOT NULL DEFAULT '1', 
`definitionId` int(21) NOT NULL, 
`resourceId` int(21) NOT NULL, 
`player_added` datetime NOT NULL, 
`player_status` enum('0','1') NOT NULL, 
`player_rating` int(11) NOT NULL, 
`Attribute1` int(11) NOT NULL, 
`Attribute2` int(11) NOT NULL, 
`Attribute3` int(11) NOT NULL, 
`Attribute4` int(11) NOT NULL, 
`Attribute5` int(11) NOT NULL, 
`Attribute6` int(11) NOT NULL, 
`available` int(11) NOT NULL, 
PRIMARY KEY (`myplayer_id`) 
) ENGINE=MyISAM AUTO_INCREMENT=3207 DEFAULT CHARSET=latin1

Answer 1

If I understand you correctly you want to add up psbin values up to 250.000 from random rows

SELECT *
FROM my_players, (SELECT @psbin := 0) x
WHERE player_type = 'IF' 
AND (@psbin := @psbin + psbin) <= 250000
ORDER BY RAND();

Answer 2

It is not easy to efficiently get N random rows from a table. My blog provides 3 efficient ways to do such, depending on details about your needs.

• You have AUTO_INCREMENT, but are there any gaps?
• Can you add another column to facilitate the random fetch?