Sort data by group

Database environment: SQL SERVER 2005

There is data 1, the field ID is ordinal, the ID is the same as a group, Createtime is the creation time. Requirements:

Createtime the group with the most recent time, and the data in the same group is sorted in descending order of createtime.

As shown in final result 2

Implementation ideas:

1. Use the analysis function to take the maximum value in the group

2. Sort by the latest time in the group and the creation time in descending order

SQL implementation:

/*test Data*/ withx0 as(SELECT   1  asID, -  asdata,CONVERT(DATETIME,'2015-09-01 12:23:56') asCreatetimeUNION  All               SELECT   1  asID, -  asdata,CONVERT(DATETIME,'2015-09-02 18:25:26') asCreatetimeUNION  All               SELECT   2  asID, the  asdata,CONVERT(DATETIME,'2015-09-01 08:46:12') asCreatetimeUNION  All               SELECT   2  asID, the  asdata,CONVERT(DATETIME,'2015-09-01 00:52:58') asCreatetimeUNION  All               SELECT   2  asID, $  asdata,CONVERT(DATETIME,'2015-09-03 10:08:36') asCreatetimeUNION  All               SELECT   3  asID, -  asdata,CONVERT(DATETIME,'2015-09-02 13:48:12') asCreatetimeUNION  All               SELECT   4  asID,108  asdata,CONVERT(DATETIME,'2015-08-31 03:11:23')             ),/*analyze functions to find the latest time in a group*/X1 as(SELECTID, data, createtime,MAX(Createtime) Over(PARTITION byID) asCreatetime_max fromx0)/*sort the result set*/    SELECTID, data, createtime fromX1ORDER  byCreatetime_maxDESC, CreatetimeDESC            

View Code

(End of this article)

Sort data by group