CODEWITHSUNDEEP
mysqlsql
Xavier
Xavier

Reputation: 131

Limit and group by rows in SQL

I have a table with items inside :

id    title     body    category_id    likes
1     title1    body1    17            57
2     title2    body2    14            35
3     title3    body3    11            16
4     title4    body4    11            96
5     title5    body5    14            78
6     title6    body6    11            64

And I want to group by this table by category_id and order by likes :

id    title     body    category_id    likes
1     title1    body1    17            57
5     title5    body5    14            78
2     title2    body2    14            35
4     title4    body4    11            96
6     title6    body6    11            64
3     title3    body3    11            16

I think I must do a subquery but how ?

Edit : I use MySQL DB

Edit 2 : I forgot to mention that I wanted to limit the results to 3 rows per category_id because I want to display the 3 most liked item per category

Upvotes: 0

Views: 89

Answers (3)

niyou
niyou

Reputation: 873

I'm sorry - it seems not to be possible in MySQL - Postgres would work. There are to limitations: 1. no limit usage allowed in subquery when using keyword IN 2. no refering to parent parent columns in subsubquery

there for the following answer does not work.

SELECT * 
FROM your_table t1
WHERE t1.id IN (SELECT id 
                FROM your_table t2 
                WHERE t1.category_id=t2.category_id 
                ORDER BY likes DESC 
                LIMIT 3)
ORDER BY category_id DESC, likes DESC

Upvotes: 0

MarcM
MarcM

Reputation: 2251

Actually, in the sample result you provided there's no real GROUP BY applied. It's just sorted by category_id and likes. To get expected result simply:

SELECT * FROM your_table
ORDER BY category_id DESC, likes DESC

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

I am interpreting the question as: I want to order by the total likes for each category.

You can use a subquery in the order by clause, so one method is:

select t.*
from t
order by (select sum(t2.likes) from t t2 where t2.category_id = t.category_id),
         category_id;

You can also do this using window functions:

select t.*
from t
order by sum(t.likes) over (partition by t.category_id),
         category_id;

Upvotes: 0

Related Questions