SQL 保留分組中的順序

我的表中有如下記錄。

我想要不同的記錄，當我按它分組時，它會失去訂單。我要維持秩序。我已經寫了下面的查詢以獲得所需的但是它不起作用：

select route_id,fixcode,fixdescription from 
route_fixcodes group by route_id,fixcode,fixdescription
having route_id = 12345 Order by fixcode

我想要如下結果：

DDL 表：

CREATE TABLE [dbo].[route_fixcodes](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [route_id] [int] NOT NULL,
 [fixcode] [varchar](4) NOT NULL,
 [fixdescription] [varchar](32) NOT NULL,
 CONSTRAINT [PK__route_fi__3213E83FD7609D27] PRIMARY KEY CLUSTERED ([id] ASC)
)

如果您沒有order by為第一個查詢的結果集指定 an，則不能保證您的數據是有序的。

看來您確實希望根據id表中的最小欄位進行排序PRIMARY KEY CLUSTERED ( [id] ASC)。

一種方法是使用CTEandMIN(id)

WITH CTE 
AS
(
SELECT route_id,fixcode,fixdescription, MIN(id) as minid
FROM route_fixcodes 
WHERE route_id = 995063 
GROUP BY route_id,fixcode,fixdescription
)
SELECT route_id,fixcode,fixdescription
FROM CTE
Order by minid;

---

測試數據

CREATE TABLE #route_fixcodes( [id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL ,route_id int,fixcode int,fixdescription nvarchar(255));

INSERT INTO #route_fixcodes(route_id,fixcode,fixdescription)
VALUES(995063,100,'Issue_Observed'),(995063,100,'Issue_Observed'),(995063,137,'Swap Altice One Pack')
,(995063,137,'Swap Altice One Pack'),(995063,247,'Defective CPE Equip.'),(995063,247,'Defective CPE Equip.')
,(995063,112,'outside coax repair'),(995063,112,'outside coax repair')

結果

route_id    fixcode fixdescription
995063  100 Issue_Observed
995063  137 Swap Altice One Pack
995063  247 Defective CPE Equip.
995063  112 outside coax repair

引用自：https://dba.stackexchange.com/questions/237525