CODEWITHSUNDEEP
mysqlgroup-by
HongyanShen
HongyanShen

Reputation: 1535

MySQL how to get first n groups?

For the follow table TestTable:

| Id | Name  | Flags |    
|----|-------|-------|    
| 1  | name1 |   1   |    
| 2  | name2 |   1   |    
| 3  | name3 |   2   |     
| 4  | name4 |   2   |    
| 5  | name5 |   3   |

how to group by Flags and return the first 2 groups. That is to say I should return rows:

|  1    |  name1  |  1  |    
|  2    |  name2  |  1  |    
|  3    |  name3  |  2  |    
|  4    |  name4  |  2  |

I tried this:

select * 
from TestTable 
group by Flags 
order by Flags 
limit 0,2

However the query returned 2 rows only.

Upvotes: 0

Views: 196

Answers (1)

trincot
trincot

Reputation: 349956

You could use a sub query to take only the first two flags, and join that:

select     t.id, t.name, t.flags
from       testtable t
inner join (select   distinct flags
            from     testtable
            order by 1
            limit    2) filter
        on filter.flags = t.flags;

See it run on regtester.com

Upvotes: 1

Related Questions