Home 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?

Questions

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?

byMR

May 23, 2023

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

the result set may return duplicates at times, I guess I could use select distict/temporary table, which would solve this issue
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);

>Solution :

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.