MySQL ORDER by two columns, limit on a single one -

is there way order results in mysql based on column , b , limit results x per values of a, in order a, (b limit x)?

assume have table_a in following format:

+------+--------+------+ | col1 |  col2  | col3 | +------+--------+------+ |    |    100 |  abc | |    |    200 |  acd | |    |    300 |  atd | |    |    400 |  aem | |    |    500 |  ieb | | b    |    150 |  aio | | b    |    250 |  loe | | b    |    350 |  wmd | | b    |    450 |  zir | | b    |    550 |  oui | +------+--------+------+ 

i obtain x highest values of column 2 associated each value of column 1. here example of result if wanted have top 3 each col1 result:

+------+--------+------+ | col1 |  col2  | col3 | +------+--------+------+ |    |    500 |  ieb | |    |    400 |  aem | |    |    300 |  atd | | b    |    550 |  oui | | b    |    450 |  zir | | b    |    350 |  wmd | +------+--------+------+ 

how achieve such behaviour without relying on 1 query per value of column 1?

try this;)

sql fiddle

create table table_a     (`col1` varchar(1), `col2` int, `col3` varchar(3)) ;  insert table_a     (`col1`, `col2`, `col3`) values     ('a', 100, 'abc'),     ('a', 200, 'acd'),     ('a', 300, 'atd'),     ('a', 400, 'aem'),     ('a', 500, 'ieb'),     ('b', 150, 'aio'),     ('b', 250, 'loe'),     ('b', 350, 'wmd'),     ('b', 450, 'zir'),     ('b', 550, 'oui') ; 

query 1:

select a.* table_a left join table_a b on a.col1 = b.col1 , a.col2 <= b.col2 group a.col1,a.col2,a.col3 having count(*) <=3 order a.col1 asc, a.col2 desc 

results:

| col1 | col2 | col3 | |------|------|------| |    |  500 |  ieb | |    |  400 |  aem | |    |  300 |  atd | |    b |  550 |  oui | |    b |  450 |  zir | |    b |  350 |  wmd |