2
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
0

2 Answers 2

4

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();
6
  • it returns empty result
    – The Beast
    Jan 3, 2016 at 17:28
  • here you can take a look @Mihai sqlfiddle.com/#!9/633b75/1
    – The Beast
    Jan 3, 2016 at 17:43
  • @JackSmith Every psbin in your sample data is larger than 250.000 so you are asking for conidtions which are not in your data,the query works sqlfiddle.com/#!9/bd690/2
    – Mihai
    Jan 3, 2016 at 17:47
  • ok so when i have psbin combined from all 3 rows it should not go pass 250k so you are saying i can not do that because all of them will pass 250k right?
    – The Beast
    Jan 3, 2016 at 17:50
  • @JackSmith Here is the query running with 3 values under 250k sqlfiddle.com/#!9/d3e8e/1 What is not clear?
    – Mihai
    Jan 3, 2016 at 17:54
0

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.