MySQL Query – How to Get a Sorted Result

execution-planMySQLPHPphpmyadmin

I am trying to get a sorted result from a MySQL query and I don't have any idea of making the query.

I have a example MySQL table as follows :

 id | cat_type
 1  |  free
 2  |  free
 3  |  free
 4  |  free
 5  |  paid
 6  |  paid
... |  ...
... |  ...
... |  ...
   and it goes on.

The result should be as follows from the above table :

 id | cat_type
 1  | free 
 2  | free 
 5  | paid

 3  | free 
 4  | free 
 6  | paid

... | free
... | free
... | paid
  and goes on(should be sorted)

The total result should be split as 3. In the first three result, first two should be free and the next one should be paid likewise all the remaining result should be sorted as two free and one paid.

The most important is that it should be sorted as two free and one paid.

Any idea how to accomplish this using a query?

Best Answer

I suppose you could do something like this from the command line/phpmyadmin:

Assuming the following table:

mysql> SELECT * FROM test;
+----+----------+
| id | cat_type |
+----+----------+
|  1 | free     |
|  2 | free     |
|  3 | free     |
|  4 | free     |
|  5 | paid     |
|  6 | paid     |
+----+----------+
6 rows in set (0.00 sec)

Make your custom ordering by selecting twice the table and using a variable as a counter:

mysql> SET @i := 0; 
SET @j := 0.5; 
SELECT id, cat_type FROM 
(SELECT @i := @i + 1 as ordering, id, cat_type FROM test WHERE cat_type = 'free' 
 UNION 
 SELECT @j := @j + 2 as ordering, id, cat_type FROM test WHERE cat_type = 'paid') 
AS base_table 
ORDER BY ordering;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+----+----------+
| id | cat_type |
+----+----------+
|  1 | free     |
|  2 | free     |
|  5 | paid     |
|  3 | free     |
|  4 | free     |
|  6 | paid     |
+----+----------+
6 rows in set (0.00 sec)

This is technically incorrect, as it could explore the original tables not in id order, but assuming you are using InnoDB, a full table scan in index order should be performed.

Also, this is terribly unoptimized:

+----+-------------------+------------+------+---------------+------+---------+------+------+-----------------+
| id | select_type       | table      | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+-------------------+------------+------+---------------+------+---------+------+------+-----------------+
|  1 | PRIMARY           | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |   12 | Using filesort  |
|  2 | DERIVED           | test       | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where     |
|  3 | UNCACHEABLE UNION | test       | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where     |
| NULL | UNION RESULT      | <union2,3> | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using temporary |
+----+-------------------+------------+------+---------------+------+---------+------+------+-----------------+
4 rows in set (0.00 sec)

To the point that if you wanted to do this in a large table, doing it programatically or using cursors would be a better idea.

Related Question