1

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?

0

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.

  • what tool did you use for this ? @jynus – Mukilan R Feb 12 '15 at 14:41
  • I am not sure what part you are referring to, but I just used the mysql command line, which is my interactive mysql client of preference, but it should work in any MySQL client, just copy the part between the "mysql>" and the ending ";" and change test to your table's name. – jynus Feb 12 '15 at 15:24
  • its working great. but i just wanted to know what tool did u used. thanks. – Mukilan R Feb 13 '15 at 11:26
  • For creating the query, I used the tool "mysql" it is a GPL text-based client for MySQL that comes builtin with the server. You can know more about it here: dev.mysql.com/doc/refman/5.6/en/mysql.html and download it here: dev.mysql.com/downloads/mysql For writing the query, I did it manually by hand, without any other tool, for the "optimization analysis", I just run EXPLAIN before the SELECT: dev.mysql.com/doc/refman/5.6/en/explain-output.html – jynus Feb 13 '15 at 11:33
  • thnq.... @jynus – Mukilan R Feb 13 '15 at 11:46

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.