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?

This can help: [In SQL, how to select the top 2 rows for each group](http://stackoverflow.com/questions/15969614/in-sql-how-to-select-the-top-2-rows-for-each-group) /// [Get top n records for each group of grouped results](http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) — Amboom, May 25 '16 at 01:42

score 3 · Accepted Answer · answered May 25 '16 at 01:53

3

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 |

answered May 25 '16 at 01:53

Blank

11,958
1
11
30

Funny I was actually reading the exact same code on this [marvelous article](http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/). Albeit it works fine, I would rather avoid it because I have to do some extraneous grouping before obtaining the initial table structure outlined in the OP, which means that I have to do the same subquery twice due to the join. – Philippe Hebert May 25 '16 at 02:31
After reviewing alternatives provided by @Amboom, your solution seems optimal. – Philippe Hebert May 25 '16 at 02:38

score 0 · Answer 2 · answered Mar 13 '20 at 20:01

The above solution answered by @Blank seems optimal if there are no duplicates in Col 2

If the schema is like as shown below:

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, 'tyu'), ('A', 200, 'acd'), ('A', 300, 'okp'), ('A', 300, 'atd'), ('A', 400, 'aem'), ('A', 500, 'ieb'), ('B', 150, 'aio'), ('B', 250, 'loe'), ('B', 350, 'wmd'), ('B', 450, 'zir'), ('B', 550, 'oui') ;

Then the result be :

col1 col2 col3

A | 500 | ieb A | 400 | aem B | 550 | oui B | 450 | zir B | 350 | wmd

It will skip the two A 300's.

See this at :http://sqlfiddle.com/#!9/454702/1/0

Any clues anyone how to overcome this?

MySQL ORDER by two columns, limit on a single one

2 Answers2

col1 col2 col3