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 |