Omid Omid - 6 months ago 10
SQL Question

MySQL, Merge selects in order of one record from each select

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

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   |
+---------------------+