By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.30 |
CREATE TABLE `tbl_match_players` (
`player_id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
`player_match_id` int NOT NULL,
`player_team` varchar(255) NOT NULL,
`player_name` varchar(255) NOT NULL,
`player_type` tinyint NOT NULL DEFAULT 1 COMMENT '1 = wicket keeper 2 = batsman 3 = all rounder 4 = bowler',
`is_compulsory` mediumint NOT NULL DEFAULT 0,
`player_points` mediumint NOT NULL DEFAULT 0
);
INSERT INTO `tbl_match_players` VALUES
( 1, 1, 'India' , 'Player 1' , 1, 0, 0),
( 2, 1, 'India' , 'Player 2' , 2, 0, 0),
( 3, 1, 'India' , 'Player 3' , 2, 0, 0),
( 4, 1, 'Netherland', 'Player 4' , 2, 0, 0),
( 5, 1, 'India' , 'Player 5' , 2, 0, 0),
( 6, 1, 'Netherland', 'Player 6' , 3, 0, 0),
( 7, 1, 'India' , 'Player 7' , 3, 0, 0),
( 8, 1, 'India' , 'Player 8' , 3, 0, 0),
( 9, 1, 'India' , 'Player 9' , 4, 0, 0),
(10, 1, 'Netherland', 'Player 10', 4, 0, 0),
(11, 1, 'India' , 'Player 11', 4, 0, 0);
Records: 11 Duplicates: 0 Warnings: 0
select GROUP_CONCAT(player_id) as batsman from ( select player_id from tbl_match_players WHERE player_type = 2 AND player_match_id = 1 order by player_id not in (1), rand() limit 3) t order by rand()
| batsman |
|---|
| 2,3,4 |