How to display top 5 and bottom 5 in same query with join?

All we need is an easy explanation of the problem, so here it is.

I’m struggling to find a way to combine these two queries into one so that only one table is viewed. I want to display the top 5 and bottom 5 categories based on the number of members in each category.

SELECT 
    category_name,
    members
From category c
JOIN grp g ON c.category_id=g.category_id
GROUP by category_name
ORDER BY members DESC
LIMIT 5;

SELECT 
    category_name,
    members
From category c
JOIN grp g ON c.category_id=g.category_id
GROUP by category_name
ORDER BY members
LIMIT 5;

I’ve tried union and multiple select statements but always end up with an error for "subquery returns more than 1 row" or inccorect syntax. Any help would be appreciated.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

If you are going to do unions with ORDER and LIMIT clauses, you will have to "hide" these clauses within subqueries:

SELECT * FROM (
    SELECT 
        category_name,
        members
    From category c
    JOIN grp g ON c.category_id=g.category_id
    GROUP by category_name
    ORDER BY members DESC
    LIMIT 5
) SQ1
UNION
SELECT * FROM (
    SELECT 
        category_name,
        members
    From category c
    JOIN grp g ON c.category_id=g.category_id
    GROUP by category_name
    ORDER BY members
    LIMIT 5
) SQ2

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

Leave a Reply