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

How to select N "special" rows if they exist, the rest has to be filled with regular rows, the total of rows can't exceed X rows in MySQL?

Supposing there’s a portal that shows 10 products on a page.
The desired goal is to show up to 3 randomly chosen "is_featured" products on the first page among other products if they exist. If not, show just 10 regular products.
The query should contain LIMIT and OFFSET as there’s a pagination. No rows should be skipped when browsing the portal and no duplicates.

The result on the first page could look similar to this (the first 3 rows are "is_featured" and chosen arbitrarily), the rest is ordered, in this case, by item_id.

item_id | author              | is_featured
129     | Bohumil Peterka     | 1
102     | Aneta Šebková       | 1
150     | Jakub Šustr         | 1
100     | Richard Kovář       | 0
101     | Růžena Staňková     | 0
103     | Lubomír Hladík      | 0
104     | Ing. Dalibor Lang   | 0
105     | Miloš Formánek      | 0
106     | Michal Hlaváček     | 0
107     | Jarmila Seidlová    | 0

2nd page would then continue with item_id 108…

What I’ve tried so far

(
  SELECT *
  FROM item
  WHERE is_featured = 1
    ORDER BY RAND()
  LIMIT 3 
)
UNION ALL
(
  SELECT *
  FROM item
  ORDER BY item_id
  LIMIT 7 
  OFFSET 0
)

There are multiple issues with my query

  1. the result set may return duplicates at times, I guess I could use select distict/temporary table, which would solve this issue

  2. if there are no "is_featured" items, the result set will only return 7 rows instead of 10

Create table

CREATE TABLE `item` (
    `item_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `author` TEXT NULL DEFAULT NULL,
    `is_featured` TINYINT(3) UNSIGNED NULL DEFAULT '1'
);

Sample data

INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (100, 'Richard Kovář', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (101, 'Růžena Staňková', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (102, 'Aneta Šebková', 1);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (103, 'Lubomír Hladík', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (104, 'Ing. Dalibor Lang', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (105, 'Miloš Formánek', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (106, 'Michal Hlaváček', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (107, 'Jarmila Seidlová', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (108, 'René Sehnal', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (109, 'Jarmila Kvapilová', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (110, 'Zdeňka Hanušová', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (111, 'Peter Štefek', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (112, 'Veronika Pšeničková', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (113, 'Ivan Hrabal', 1);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (114, 'Vladimíra Pavlíčková', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (115, 'Lenka Dušková', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (116, 'Hana Bendová', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (117, 'Radim Horváth', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (118, 'Dana Smržová', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (119, 'Romana Divišová', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (120, 'Ondřej Kropáček', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (121, 'Alena Šebestová', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (122, 'Matěj Kurka', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (123, 'Andrea Rambousková', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (124, 'Alena Kaňová', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (125, 'Vladimír Sládek', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (126, 'Šárka Smrčková', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (127, 'Jiřina Papežová', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (128, 'Radomír Martínek', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (129, 'Bohumil Peterka', 1);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (130, 'Bc. Karel Vejvoda', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (131, 'Jiří Hladík', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (132, 'Miluše Holečková', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (133, 'Jaromír Mareš', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (134, 'MVDr. Marcela Šafářová', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (135, 'Rudolf Duda', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (136, 'Irena Husáková', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (137, 'Simona Bednářová', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (138, 'MUDr. Peter Landa', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (139, 'Kristýna Hynková', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (140, 'Helena Kudrnová', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (141, 'Tomáš Hájek', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (142, 'Jindřich Ulrich', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (143, 'Vlastimil Sobek', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (144, 'Ivo Lacina', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (145, 'JUDr. Nela Králová', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (146, 'Alena Horká', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (147, 'Dalibor Žižka', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (148, 'Aneta Mráčková', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (149, 'Ondřej Holík', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (150, 'Jakub Šustr', 1);
Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    This is an alternative solution to Tim's answer for those who can't use CTEs.

    CREATE TEMPORARY TABLE item_tmp(item_id INT);
CREATE TEMPORARY TABLE item_tmp2(item_id INT);
INSERT INTO item_tmp (item_id) SELECT item_id from item WHERE is_featured = 1 ORDER BY RAND() LIMIT 3;  
INSERT INTO item_tmp2 (item_id) SELECT item_id from item_tmp;  
    
SELECT item_id, author, is_featured
FROM
(
    (SELECT item.*, 1 AS pos FROM item INNER JOIN item_tmp ON item_tmp.item_id = item.item_id)
    UNION ALL
    (
        SELECT *, 2
        FROM item i1
        WHERE NOT EXISTS (
            SELECT 1
            FROM item_tmp2 i2
            WHERE i2.item_id = i1.item_id
        )
    )
) t
ORDER BY pos, item_id
LIMIT 0,10;
 
DROP TEMPORARY TABLE item_tmp;
DROP TEMPORARY TABLE item_tmp2;

  2. 0

    You may try excluding the featured duplicates, if they exist, by using exist logic:

    WITH cte AS (
    SELECT *
    FROM item
    WHERE is_featured = 1
    ORDER BY RAND()
    LIMIT 3 
)

SELECT item_id, author, is_featured
FROM
(
    (SELECT *, 1 AS pos FROM cte)
    UNION ALL
    (
        SELECT *, 2
        FROM item i1
        WHERE NOT EXISTS (
            SELECT 1
            FROM cte i2
            WHERE i2.item_id = i1.item_id
        )
    )
) t
ORDER BY pos, item_id
LIMIT 10;

    The above approach uses a computed column pos which always puts the (up to) 3 featured records ahead of those that follow. Should there be fewer than 3 featured records, then the remainder would be filled by the second half of the union query.

    Login or Signup to reply.
  3. 0

    You can use instead of
    UNION ALL
    (
    SELECT *
    FROM item
    ORDER BY item_id
    LIMIT 7
    OFFSET 0
    )

    To this:
    UNION ALL
    (
    SELECT *
    FROM item
    WHERE is_featured = 0
    ORDER BY item_id
    LIMIT 10
    )

    As this would help you to is_featured = 0 and orders them by item_id. By using LIMIT and ORDER BY is done because to ensure that no rows are being scanned ad result remains consistent

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

Back To Top
Search