CODEWITHSUNDEEP
mysqlsql
Speise
Speise

Reputation: 809

How to select certain numbers of groups in MySQL?

I have the table with data: ID| And for this table I need to create pegination by productId column. I know about LIMIT N,M, but it works with rows and not with groups. For examle for my table with pegination = 2 I expect to retrieve all 9 records with productId = 1 and 2 (the number of groups is 2).

So how to create pegination by numbers of groups ?

I will be very thankfull for answers with example.

Upvotes: 0

Views: 79

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270191

One way to do pagination by groups is to assign a product sequence to the query. Using variables, this requires a subquery:

select t.*
from (select t.*,
             (@rn := if(@p = productid, @rn + 1,
                        if(@rn := productid, 1, 1)
                       )
             ) as rn
      from table t cross join
           (select @rn := 0, @p := -1) vars
      order by t.productid
     ) t
where rn between X and Y;

With an index on t(productid), you can also do this with a subquery. The condition can then go in a having clause:

select t.*,
       (select count(distinct productid)
        from t t2
        where t2.productid <= t.productid)
       ) as pno
from t
having pno between X and Y;

Upvotes: 1

jebau
jebau

Reputation: 11

Try this:

select * from
(select * from <your table> where <your condition> group by <with your group>)
LIMIT number;

Upvotes: 1

Related Questions