mysql case not working to get items shown first

Question

trying to get rows that have a certain field value to show first in the results. I think I have it right but it doesn't seem to work:

SELECT * FROM
            (
                SELECT a.article_id, a.`category_name`, a.`category_id`,
                @rank1 := IF( @val = a.article_id, @rank1 +1, 1 ) AS rank1,
                @val := a.article_id
                FROM
                (
                    SELECT r.article_id, c.`category_name`, c.`category_id`
                    FROM  `article_category_reference` r
                    INNER JOIN  `articles_categorys` c ON c.category_id = r.category_id
                    WHERE r.article_id IN (?,?,?,?)
                    ORDER BY r.`article_id`, CASE WHEN (c.`show_first` = 1) THEN 0 ELSE 1 END ASC, c.category_name ASC
                ) AS a
            )as Z
            WHERE Z.rank1 < 5

Explanation: This is to get a list of tags for all articles on the current page of a blog, and limit each to 4.

I thought checking "show_first" = 1 would work but it seems to do nothing in this case.

mysql Ver 15.1 Distrib 10.4.11-MariaDB

Please provide sample data and expected results to clarify your requirement. Also, which version of MySQL are you running? — GMB, Jan 17 '20 at 10:38
If you want to use variables for to refer to previous values you MUST specify proper ORDER BY on the same subquery level. Ordering in the inner subquery will be ignored anycase. — Akina, Jan 17 '20 at 10:38

score 1 · Accepted Answer · answered Jan 17 '20 at 10:52

1

If you're using MariaDB 10.4 you can use CTEs and window functions, which will make this much easier. Without sample data it's hard to be certain, but this should be close:

WITH CTE AS (
    SELECT r.article_id, c.`category_name`, c.`category_id`,
           ROW_NUMBER() OVER (PARTITION BY r.`article_id` ORDER BY c.show_first DESC, c.category_name) AS rn
    FROM  `article_category_reference` r
    INNER JOIN  `articles_categorys` c ON c.category_id = r.category_id
    WHERE r.article_id IN (?,?,?,?)
)
SELECT article_id, category_name, category_id
FROM CTE
WHERE rn < 5

answered Jan 17 '20 at 10:52

Nick

138,499
22
57
95

Okay, so this is actually brilliant. It's so much clearer thank the variable ranking way and works perfectly to limit a subquery. Thank you!! – NaughtySquid Jan 17 '20 at 12:06
@NaughtySquid no worries. It's well worth the effort learning how to use ctes and window functions in MariaDB (not to mention the vastly improved regex functions). – Nick Jan 17 '20 at 20:36
@NaughtySquid I think you should un-delete your answer, if someone else sees this question who doesn't have access to window functions it could be useful to them. – Nick Jan 17 '20 at 20:36

score 0 · Answer 2 · answered Jan 17 '20 at 10:57

0

The answer was actually in here MySQL/MariaDB - order by inside subquery

The order by doesn't work inside the subquery, had to work it in outside it.

answered Jan 17 '20 at 10:57

NaughtySquid

1,947
3
29
44

1

You should really look into window functions and ctes, they make this type of query much easier. Also the use of variables like this is deprecated and will be removed in some future version of MySQL/MariaDB – Nick Jan 17 '20 at 11:05

mysql case not working to get items shown first

2 Answers2