MySQL ORDER by two columns, limit on a single one

Question

Is there a way to ORDER results in MySQL based on a column A and B and then limit the results to X per values of A, as in ORDER BY A, (B LIMIT X)?

Assume I have table_A in the following format:

+------+--------+------+
| Col1 |  Col2  | Col3 |
+------+--------+------+
| 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 |
+------+--------+------+

I would like to obtain the X highest values of column 2 associated with each value of column 1. Here is an example of the result if I wanted to have the top 3 for each col1 result:

+------+--------+------+
| Col1 |  Col2  | Col3 |
+------+--------+------+
| A    |    500 |  ieb |
| A    |    400 |  aem |
| A    |    300 |  atd |
| B    |    550 |  oui |
| B    |    450 |  zir |
| B    |    350 |  wmd |
+------+--------+------+

How could I achieve such a behaviour without relying on one query per value of the column 1?

Answer 1

Try this;)

SQL Fiddle

CREATE TABLE table_A
    (`Col1` varchar(1), `Col2` int, `Col3` varchar(3))
;

INSERT INTO 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.*
from table_A a
left join table_A b on a.Col1 = b.Col1 and a.Col2 <= b.Col2
group by a.Col1,a.Col2,a.Col3
having count(*) <=3
order by a.Col1 asc, a.Col2 desc

Results:

| Col1 | Col2 | Col3 |
|------|------|------|
|    A |  500 |  ieb |
|    A |  400 |  aem |
|    A |  300 |  atd |
|    B |  550 |  oui |
|    B |  450 |  zir |
|    B |  350 |  wmd |