Simplify SQL query of multiple SELECT statements, each with UNION and LIMIT

Question

Need assistance with simplifying this SQL query to possibly a single SELECT:

(SELECT * FROM `deals`
WHERE category_id = 1
ORDER BY id desc
LIMIT 10)
UNION
(SELECT * FROM `deals`
WHERE category_id = 2
ORDER BY id desc
LIMIT 10)
UNION
(SELECT * FROM `deals`
WHERE category_id = 4
ORDER BY id desc
LIMIT 10)
UNION
(SELECT * FROM `deals`
WHERE category_id = 5
ORDER BY id desc
LIMIT 10)
UNION
(SELECT * FROM `deals`
WHERE category_id = 6
ORDER BY id desc
LIMIT 10)
UNION
(SELECT * FROM `deals`
WHERE category_id = 8
ORDER BY id desc
LIMIT 10)
UNION
(SELECT * FROM `deals`
WHERE category_id = 9
ORDER BY id desc
LIMIT 10)
UNION
(SELECT * FROM `deals`
WHERE category_id = 10
ORDER BY id desc
LIMIT 10)
UNION
(SELECT * FROM `deals`
WHERE category_id = 17
ORDER BY id desc
LIMIT 10)

I've been told to try using GROUP BY and HAVING. However, any query I tried didn't work in the slightest...

Any help will be greatly appreciated!

EDIT - apologies, forgot to mention database engine is MySQL

It is a single select already, isn't it? – Cetin Basoz Jul 31 '18 at 14:38 — Cetin Basoz, Jul 31 '18 at 14:38

score 1 · Answer 1 · answered Jul 31 '18 at 14:44

1

You can condense this down with a window function to limit each group bucket to 10.

SELECT
    *
FROM
(
    SELECT 
        *,
        ROW_NUMBER() OVER PARTITION BY(category_id ORDER BY id DESC) AS GroupOrder
    FROM `deals`
    WHERE category_id BETWEEN 1 AND 10
)AS X
WHERE
    GroupOrder<=10

answered Jul 31 '18 at 14:44

Ross Bush

14,648
2
32
55

1

You did not mention your RDMS. If MySQL then this article discusses how to emulate ROW_NUMBER. https://www.log4code.com/alternative-to-row_number-in-mysql/ – Ross Bush Jul 31 '18 at 14:47
Note that if you're using MySQL, you must be using MySQL 8.0 or later to have access to the ROW_NUMBER() function. For MariaDB, you must be using 10.2.0 or later. Most other RDBMSs have had this function for many years. – Bacon Bits Jul 31 '18 at 14:48
I think this works out too , rank if not row_number may work too.... just one thing Ross forgot the category_id = 17, Assuming he didnt scroll down the code ;) – Wired604 Jul 31 '18 at 15:00

score 0 · Answer 2 · answered Jul 31 '18 at 14:41

0

I'm not sure, I need to know if you need that limit of 10, is this like take top 10 of all those things?

if not then

SELECT * FROM `deals`
WHERE category_id between 0 and 10 or category_id=17
ORDER BY category_id asc,  id desc

answered Jul 31 '18 at 14:41

Wired604

370
1
3
10

The limit is completely necessary but would be nice to include. It's exactly like that, pulling top 10 items from the table – Liel van der Hoeven Jul 31 '18 at 14:54
Do you have another field that counts them or something like that ? that way we could use it to keep the top 10....SELECT * FROM `deals` WHERE id > 11 and (category_id between 0 and 10 or category_id=17) ORDER BY category_id asc, id desc – Wired604 Jul 31 '18 at 14:56
maybe use rank... where RANK ( ) OVER ( partition by category_id order by id desc ) < 10 – Wired604 Jul 31 '18 at 14:57

score 0 · Answer 3 · answered Jul 31 '18 at 15:01

For older versions of MySQL without the windowing functions, here is the code.

SELECT T1.ID, T1.Category_ID, T1.Name
FROM (
SELECT  @row_num := IF(@prev_value=concat_ws('',t.Category_ID),@row_num+1,1) AS RowNumber
         ,t.*
         ,@prev_value := concat_ws('',t.Category_ID)
    FROM data t,
         (SELECT @row_num := 1) x,
         (SELECT @prev_value := '') y
   ORDER BY t.Category_ID
   ) T1
  WHERE T1.RowNumber < 10
  AND T1.Category_ID IN (1,2,3,4,5,6,7,8,9,10)

You will need to add the necessary field names to the other select.

This uses the technique described here

Simplify SQL query of multiple SELECT statements, each with UNION and LIMIT

3 Answers3