MySQL，Merge按每个选择的一条记录顺序选择

Question

I have a table that contains too many records and each bunch of records belong to someone:

---------------------
id | data | username
---------------------
1  | 10   | ali
2  | 11   | ali
3  | 12   | ali
4  | 20   | omid
5  | 21   | omid
6  | 30   | reza

now I want to create a query to result me like this:

1-10-ali
4-20-omid
6-30-reza
2-11-ali
5-21-omid
3-12-ali

Is there anyway to create a query to result me one record per each username and then one from another, and another to the end?

Answer 1

Unfortunately MySQL doesn't have a ranking system so you can use UDV (user defined variables) to rank your records like so.

SELECT id, `data`, name
FROM
( SELECT 
    id, `data`, name,
    @rank := if(@name = name, @rank + 1, 1) as rank,
    @name := name
  FROM test
  CROSS JOIN (SELECT @rank := 1, @name := '') temp
  ORDER BY name, `data`
) t
ORDER BY t.rank, t.name, t.data

Sql Fiddle to play with

Output:

+---------------------+
| id  | data |  name  |
+-----+------+--------+
|  1  |  10  |  ali   |
+---------------------+
|  4  |  20  |  omid  |
+---------------------+ 
|  6  |  30  |  reza  |
+---------------------+ 
|  2  |  11  |  ali   |
+---------------------+
|  5  |  21  |  omid  |
+---------------------+ 
|  3  |  12  |  ali   |
+---------------------+