求sql大神看看这个咋搞。sql分组排序的问题。

Question

数据表 user

| id | name | time |

| 1 | aaa | 1 |

| 2 | bbb | 2 |

| 3 | ccc | 3 |

| 4 | aaa | 4 |

| 5 | ccc | 5 |

要求结果为：

| id | name | time |

| 5 | ccc | 5 |

| 3 | ccc | 3 |

| 4 | aaa | 4 |

| 1 | aaa | 1 |

| 3 | bbb | 3 |

需求是：
将最大的time对应的相同name全部置顶。
比方说最大的time是5，对应的name是ccc。就将所有name为ccc的排序在最前面。
然后第二个aaa的time为4，就将所有name为aaa的排在ccc的后面。

(select * from (
select * from
(user as u , (SELECT id,name, max(time) FROM user) mx)
WHERE u.id = mx.id
ORDER BY
u.time DESC) table1 )

UNION

(select * from
(SELECT * FROM
user as u
ORDER BY
u.time DESC) table2 )

楼主只完成了置顶ccc。剩余的就按时间倒序排序了。aaa那个怎么也想不出来咋做。求大神解答！！

Answer 1

1.优先按name列排序，然后是time列
2.name需要先把排序的顺序进行计算

try this

 select * from user order by find_in_set(name,
 (select group_concat(name order by time desc)  from 
 (select name,max(time) as time from user group by name) as tt),time desc;