2

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)
)
7
  • 1
    Can you please post the query that you are using for group by. May 7 '19 at 6:52
  • select route_id,fixcode,fixdescription from route_fixcodes group by route_id,fixcode,fixdescription having route_id = 12345 Order by fixcode
    – kkakadiya
    May 7 '19 at 6:53
  • What is the use of group by here when there is no any aggregate function? can you also please include DDL of route_fixcodes table. May 7 '19 at 6:58
  • group by is used to get distinct records. DDL route_fixcodes SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO 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 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
    – kkakadiya
    May 7 '19 at 7:29
  • 1
    What is the actual order you want? Looking at the screenshots, data is not ordered numerically by fix code, or alphabetically by fixdescription, in your expected output. What column do you want to order the data by?
    – HandyD
    May 7 '19 at 7:41
1

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
2
  • above solution doesn't work.I don't want my output in any of the order. I want output as it displayed in screenshot
    – kkakadiya
    May 7 '19 at 8:34
  • thanks @Randi Vertongen for the help. there is nothing like no solution. I got the solution from above your partial query. here is my query. select route_id,fixcode,fixdescription, MIN(id) as minid from route_fixcodes where route_id = 995063 GROUP BY route_id,fixcode,fixdescription Order by minid
    – kkakadiya
    May 7 '19 at 9:29
0

Try This

select 
    route_id,fixcode as fx,fixdescription 
from 
    route_fixcodes 
group by 
    route_id,fixcode ,fixdescription 
having 
    route_id = 12345 Order by fx
3
  • suggestion doesn't work. Order by in last doesn't maintain my original Order.
    – kkakadiya
    May 7 '19 at 7:26
  • I don't want my output in any of the order. I want output as it displayed in screenshot.
    – kkakadiya
    May 7 '19 at 8:23
  • 7
    @kkakadiya, a table is an unordered set of rows. The order of data in you screen image can differ with the same data depending on how SQL server chose to return the rows. Order by is required if you need data returned in a particular sequence.
    – Dan Guzman
    May 7 '19 at 8:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.