Sql-server – SQL Preserve the Order in group by

group byorder-byquerysql server

I have records like below in my table.

enter image description here

I want distinct records and when I do group by it loses the Order. I want to maintain the order. I have written below query to get the desired however its not working:

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

I want Result like below:

enter image description here

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

Best Answer

If you do not specify an order by for the result set of your first query then your data is not guaranteed to be ordered.

It does appear that you want to order by the minimal id field in your table based on PRIMARY KEY CLUSTERED ( [id] ASC).

One way would be by using a CTE and MIN(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;

Test data

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

Result

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