Group the records and sort groups in itself

Question

I have 2 columns in my pricing table: car_id and price

I want to

• Group the records with same car_ids and sort these groups by price in itself. (namely, all records with the same car_id must be listed consecutively.)

• I want to sort these groups by minimum price they include.

For example,

car_id | price
---------------
1      |   2
1      |   5
2      |   3
2      |   1
2      |   10
3      |   7
3      |   3
3      |   8

should be sorted like this:

car_id | price
---------------
2      |   1
2      |   3
2      |   10
1      |   2 
1      |   5 
3      |   3 
3      |   7 
3      |   8

How can I write a query that performs this operation? I am using MySQL V5.6.

Answer 1

You can use a subquery that returns the min price for each car_id in the ORDER BY clause:

select t.*
from tablename t
order by 
  (select min(price) from tablename where car_id = t.car_id), 
  car_id, 
  price

See the demo.
Or join the table to the subquery that returns all the min prices:

select t.*
from tablename t inner join (
  select car_id, min(price) minprice
  from tablename 
  group by car_id
) m on m.car_id = t.car_id
order by 
  m.minprice, 
  t.car_id, 
  t.price 

See the demo.
Results:

| car_id | price |
| ------ | ----- |
| 2      | 1     |
| 2      | 3     |
| 2      | 10    |
| 1      | 2     |
| 1      | 5     |
| 3      | 3     |
| 3      | 7     |
| 3      | 8     |

Answer 2

You may use a subquery since the 5.6 does not support the window functions.

select *, 
  (
    select min(price) 
    from datatab d2
    where d1.car_id = d2.car_id
  ) min_price
from datatab d1
order by min_price, car_id, price

DBFIDDLE DEMO

Answer 3

You can use window functions in the order by:

select p.*
from pricing p
order by min(price) over (partition by car_id),
         car_id;

The car_id handles the case when multiple car_ids have the same minimum price.

In older versions of MySQL, you can do something similar with a subquery:

select p.*
from pricing p
order by (select min(p2.price) from pricing p2 where p2.car_id = p.car_id),
         car_id;