CODEWITHSUNDEEP
mysqlsql-order-bysql-limit
Philippe Hebert
Philippe Hebert

Reputation: 2028

MySQL ORDER by two columns, limit on a single one

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?

Upvotes: 1

Views: 2746

Answers (2)

Sindhu Kasula
Sindhu Kasula

Reputation: 1

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?

Upvotes: 0

Blank
Blank

Reputation: 12378

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 |

Upvotes: 3

Related Questions