Question 292416 in Mysql Question posted in
The official MySQL documentation can be found here.

Compulsary include during select with Random in MYSQL Query

I am selecting random id with my query like this

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()

Currently its working fine as per my need but now I have added one column in table called is_compulsory when any row have value called 1, I want always return that id with my result as compulsory and it should not cross the limit.

For example, I have id called 2 and 3 have assigned is_compulsory = 1, Now in my result I want compulsory include that two ids and then remain will be selected with random. So expected result should be like

example 1

2,3,5

example 2
2,3,4

My Table is like this

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, 1, 0),
( 3, 1, 'India'     , 'Player 3' , 2, 1, 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);

This is DBFiddle Link

Thanks!

Tags:

2

Answers


  1. 0

    You can achieve this by modifying your query to ensure that the compulsory players are always included in the result.

    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 is_compulsory DESC, RAND()
    LIMIT 3
)
ORDER BY RAND();
    Login or Signup to reply.
  2. 0

    We can try a union approach here:

    SELECT *
FROM
(SELECT *, 1 AS pos FROM tbl_match_players WHERE is_compulsory = 1)
UNION ALL
(SELECT *, 2 FROM tbl_match_players WHERE is_compulsory = 0 ORDER BY RAND() LIMIT 1)
ORDER BY pos, is_compulsory <> 1, player_id
LIMIT 4;

    The second subquery in the union will return one random non compulsory record.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search