In my database I have:
Code: Select all
id name weight
1 Pezm 1
2 Shog 1.1
3 Ebay 1.2
4 PLp 1.3
5 php 1.4How can this be acchieved?
Moderator: General Moderators
Code: Select all
id name weight
1 Pezm 1
2 Shog 1.1
3 Ebay 1.2
4 PLp 1.3
5 php 1.4Code: Select all
SELECT
*
FROM
myTable
ORDER BY
RAND(weight)
Code: Select all
CREATE TABLE `rand` (
`id` int(11) NOT NULL auto_increment,
`item` varchar(255) NOT NULL default '',
`weight` float NOT NULL default '0',
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
INSERT INTO `rand` (`id`, `item`, `weight`) VALUES
(1, 'item 1', 1),
(2, 'item 2', 2.5),
(3, 'item 3', 1.3),
(4, 'item 4', 0);
SELECT * , (
SELECT sum( weight )
FROM `rand` AS t_sum
WHERE t_sum.id >
t.id
) AS weight_sum, (
SELECT sum( weight )
FROM `rand`
) AS weight_total
FROM `rand` AS t
WHERE weight !=0
AND rand( ) * (
SELECT SUM( weight )
FROM `rand` ) > = (
SELECT sum( weight )
FROM `rand` AS t_sum
WHERE t_sum.id >
t.id ) ORDER BY t.id ASC