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
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();
  • it returns empty result – The Beast Jan 3 '16 at 17:28
  • here you can take a look @Mihai sqlfiddle.com/#!9/633b75/1 – The Beast Jan 3 '16 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 '16 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 '16 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 '16 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, privacy policy and cookie policy

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