CODEWITHSUNDEEP
mysqlsql
Swami Shirsath
Swami Shirsath

Reputation: 23

How to select limited rows based on column

I'm new in MySQL. This may be easy, but out of my logic

I have a product_table which has 5 columns 

id
product_name
product_image
description
category_id

in this table category_id has multiple rows with the same id

for example

category field has 300 rows with category_id '4' and 100 rows with category_id '3' so on....

I want to select only 5 rows per category_id for example 5 rows for category_id 4 and 5 rows for category_id 3 like this.

Upvotes: 0

Views: 36

Answers (2)

StanislavL
StanislavL

Reputation: 57381

select * 
from 
    (SELECT category_id,
        id,
        CASE WHEN @gr=category_id THEN @rn:=@rn+1 ELSE @rn:=0 END as row_number,
        @gr:=category_id
     FROM the_table, (select @rn:=0, @gr:=null) as sess
     order by category_id, id) sub
WHERE sub.row_number<5

You can introduce session variables and order your data counting member in each group. Then leave only members with group number less than 5

Upvotes: 0

lamandy
lamandy

Reputation: 982

SELECT * FROM product_table WHERE category_id = '1' LIMIT 5
UNION
SELECT * FROM product_table WHERE category_id = '2' LIMIT 5
UNION
SELECT * FROM product_table WHERE category_id = '3' LIMIT 5
.
.
.

If there are just a few category, then this should be fine.

Upvotes: 1

Related Questions