CODEWITHSUNDEEP
mysqlsqlunionsql-limit
Liel van der Hoeven
Liel van der Hoeven

Reputation: 73

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

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

Upvotes: 0

Views: 202

Answers (3)

LeeG
LeeG

Reputation: 728

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

Upvotes: 0

Ross Bush
Ross Bush

Reputation: 15185

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

Upvotes: 1

Wired604
Wired604

Reputation: 370

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

Upvotes: 0

Related Questions