SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE TestData (
XTypeNo int NULL , 
Xtype nvarchar(40) NULL , 
XDateTime DateTime NULL ,
Xvalue float NULL); 
--假設資料樣本如下
INSERT INTO TestData (XTypeNo,Xtype,XDateTime,Xvalue)
VALUES 
(1,'TypeA','2020-2-8 06:42:24AM',10),
(2,'TypeC','2020-2-6 10:17:12AM',20),
(3,'TypeB','2021-2-6 03:14:33AM',10),
(4,'TypeC','2020-2-3 09:33:04PM',20),
(5,'TypeC','2021-2-3 09:33:04PM',50),
(6,'TypeA','2021-2-3 05:07:12AM',20),
(7,'TypeB','2020-1-20 11:01:53PM',30),
(8,'TypeB','2021-1-19 07:37:54PM',90),
(9,'TypeA','2020-11-7 12:11:49AM',30),
(10,'TypeA','2020-10-5 07:25:28PM',70),
(11, 'TypeB', '2021-2-5 08:14:33AM',12);

SELECT *
FROM TestData;


XTypeNo
Xtype
XDateTime
Xvalue


1
TypeA
2020-02-08 06:42:24.000
10

2
TypeC
2020-02-06 10:17:12.000
20

3
TypeB
2021-02-06 03:14:33.000
10

4
TypeC
2020-02-03 21:33:04.000
20

5
TypeC
2021-02-03 21:33:04.000
50

6
TypeA
2021-02-03 05:07:12.000
20

7
TypeB
2020-01-20 23:01:53.000
30

8
TypeB
2021-01-19 19:37:54.000
90

9
TypeA
2020-11-07 00:11:49.000
30

10
TypeA
2020-10-05 19:25:28.000
70

11
TypeB
2021-02-05 08:14:33.000
12


SELECT *
FROM TestData
ORDER BY XDateTime DESC;


XTypeNo
Xtype
XDateTime
Xvalue


3
TypeB
2021-02-06 03:14:33.000
10

11
TypeB
2021-02-05 08:14:33.000
12

5
TypeC
2021-02-03 21:33:04.000
50

6
TypeA
2021-02-03 05:07:12.000
20

8
TypeB
2021-01-19 19:37:54.000
90

9
TypeA
2020-11-07 00:11:49.000
30

10
TypeA
2020-10-05 19:25:28.000
70

1
TypeA
2020-02-08 06:42:24.000
10

2
TypeC
2020-02-06 10:17:12.000
20

4
TypeC
2020-02-03 21:33:04.000
20

7
TypeB
2020-01-20 23:01:53.000
30


with t1 as (
select Xtype
     , max(XDateTime) maxdt
  from TestData
 group by Xtype
), t2 as (
select Xtype
     , rank() over(order by maxdt desc) ord
  from t1
)
select d.*
  from TestData d
  join t2
    on d.Xtype = t2.Xtype
 order by t2.ord, XDateTime desc
;


XTypeNo
Xtype
XDateTime
Xvalue


3
TypeB
2021-02-06 03:14:33.000
10

11
TypeB
2021-02-05 08:14:33.000
12

8
TypeB
2021-01-19 19:37:54.000
90

7
TypeB
2020-01-20 23:01:53.000
30

5
TypeC
2021-02-03 21:33:04.000
50

2
TypeC
2020-02-06 10:17:12.000
20

4
TypeC
2020-02-03 21:33:04.000
20

6
TypeA
2021-02-03 05:07:12.000
20

9
TypeA
2020-11-07 00:11:49.000
30

10
TypeA
2020-10-05 19:25:28.000
70

1
TypeA
2020-02-08 06:42:24.000
10


CREATE TABLE ExpectSorData (
XTypeNo int NULL , 
Xtype nvarchar(40) NULL , 
XDateTime DateTime NULL ,
Xvalue float NULL); 
--預期得到的結果=>範例資料
INSERT INTO ExpectSorData (XTypeNo,Xtype,XDateTime,Xvalue)
VALUES 
(3,'TypeB','2021-2-6 03:14:33AM',10),
(8,'TypeB','2021-1-19 07:37:54PM',90),
(7,'TypeB','2020-1-20 11:01:53PM',30),
(5,'TypeC','2021-2-3 09:33:04PM',50),
(2,'TypeC','2020-2-6 10:17:12AM',20),
(4,'TypeC','2020-2-3 09:33:04PM',20),
(6,'TypeA','2021-2-3 05:07:12AM',20),
(9,'TypeA','2020-11-7 12:11:49AM',30),
(10,'TypeA','2020-10-5 07:25:28PM',70),
(1,'TypeA','2020-2-8 06:42:24AM',10);

SELECT *
FROM ExpectSorData;


XTypeNo
Xtype
XDateTime
Xvalue


3
TypeB
2021-02-06 03:14:33.000
10

8
TypeB
2021-01-19 19:37:54.000
90

7
TypeB
2020-01-20 23:01:53.000
30

5
TypeC
2021-02-03 21:33:04.000
50

2
TypeC
2020-02-06 10:17:12.000
20

4
TypeC
2020-02-03 21:33:04.000
20

6
TypeA
2021-02-03 05:07:12.000
20

9
TypeA
2020-11-07 00:11:49.000
30

10
TypeA
2020-10-05 19:25:28.000
70

1
TypeA
2020-02-08 06:42:24.000
10

XTypeNo	Xtype	XDateTime	Xvalue
1	TypeA	2020-02-08 06:42:24.000	10
2	TypeC	2020-02-06 10:17:12.000	20
3	TypeB	2021-02-06 03:14:33.000	10
4	TypeC	2020-02-03 21:33:04.000	20
5	TypeC	2021-02-03 21:33:04.000	50
6	TypeA	2021-02-03 05:07:12.000	20
7	TypeB	2020-01-20 23:01:53.000	30
8	TypeB	2021-01-19 19:37:54.000	90
9	TypeA	2020-11-07 00:11:49.000	30
10	TypeA	2020-10-05 19:25:28.000	70
11	TypeB	2021-02-05 08:14:33.000	12