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);

CREATE TABLE TYPEORDER (
  ID int null,
  Xtype nvarchar(40) null
)
INSERT INTO TYPEORDER (ID,Xtype) VALUES
(1 , 'TypeB'),(2,'TypeC'),(3,'TypeA')

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


with T1 AS(
  SELECT *
  FROM TestData

)
--依時間排序,最新時間的Type依序為B->C->A
SELECT *
FROM T1
LEFT JOIN TYPEORDER T2 ON T1.Xtype = T2.Xtype
ORDER BY T2.ID ,T1.XDatetime desc


XTypeNo
Xtype
XDateTime
Xvalue
ID
Xtype


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

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

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

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

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

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

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

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

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

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


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