SQLTeam.com | Weblogs | Forums

How to Display FeatureName of Comptitor First then Npx Feature Name second Based On DisplayOrder?

How to Display Feature Name of Comptitor First then NXP Feature Name second Based On Display Order?

I work on SQL SERVER 2012 I face issue I can't arrange feature on same display order to start by comptitor feature name then nxp
no issue on display order 1 and 2 because it is correct
issue exist on display order 3
so if i have more than one features have same display order then i need all features have same display Order
to be arranged as :

comptitor feature

Nxp feature

issue I face here all comptitor feature come first then nxp second for same display order and this wrong

so wrong is features will display for same display order as :

comptitor function
comptitor type
nxp function
nxp type

correct is features will display for same display order as :
comptitor function
nxp function
comptitor type
nxp type

ddl and insert statment
USE [ExtractReports]
GO
/ Object: Table [dbo].[FeaturesOrder] Script Date: 4/15/2021 4:52:17 AM /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FeaturesOrder](
[FeatureName] [nvarchar](511) NULL,
[DisplayOrder] [int] NULL
) ON [PRIMARY]

 GO
 INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'Competitor Accelerometers Type', 3)
 INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'Competitor Battery Type', 3)
 
 INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'Competitor Function', 3)
 INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'Competitor Multiplexer And Demultiplexer', 3)
 INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'NXP Type', 3)
 INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'Competitor Type', 3)
 INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'NXP Automotive', 1)
 INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'NXP Diode Type', 3)
 INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'NXP Normalized Package Name', 2)
 INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'Competitor Automotive', 1)
 INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'NXP Accelerometers Type', 3)
 INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'NXP Amplifier Type', 3)
 INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'NXP Battery Type', 3)
 INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'NXP Function', 3)
 INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'NXP Multi-Demultiplexer Circuit', 3)
 INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'NXP Multiplexer And Demultiplexer', 3)
 INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'NXP Output Type', 3)
 INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'Competitor Amplifier Type', 3)
 INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'Competitor Diode Type', 3)
 INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'Competitor Multi-Demultiplexer Circuit', 3)
 INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'Competitor Normalized Package Name', 2)
 INSERT [dbo].[FeaturesOrder] ([FeatureName], [DisplayOrder]) VALUES (N'Competitor Output Type', 3)

Expected result as below :slight_smile:

FeatureName displayorder
Competitor Automotive 1
NXP Automotive 1
Competitor Normalized Package Name 2
NXP Normalized Package Name 2
Competitor Accelerometers Type 3
NXP Accelerometers Type 3
Competitor Battery Type 3
NXP Battery Type 3
Competitor Function 3
NXP Function 3
Competitor Multiplexer And Demultiplexer 3
NXP Multiplexer And Demultiplexer 3
Competitor Type 3
NXP Type 3
Competitor Multi-Demultiplexer Circuit 3
NXP Multi-Demultiplexer Circuit 3
Competitor Amplifier Type 3
NXP Amplifier Type 3
Competitor Diode Type 3
NXP Diode Type 3
Competitor Output Type 3
NXP Output Type 3

what i try to get expected result :slight_smile:

SELECT   FeatureName,displayorder 
 FROM   [ExtractReports].[dbo].[FeaturesOrder]  with(nolock)
 group by FeatureName,displayorder
 ORDER BY  displayorder ASC,FeatureName asc

You can try this:

SELECT FeatureName,displayorder, REPLACE(REPLACE(FeatureName,'NXP',''),'Competitor','')
FROM [dbo].[FeaturesOrder] with(nolock)
group by FeatureName,displayorder
ORDER BY displayorder ASC, REPLACE(REPLACE(FeatureName,'NXP',''),'Competitor','') asc, FeatureName asc

Also be sure that you know when to use with(nolock) and when not. I cannot judge if it's needed or not :wink:

hi hope this helps

SELECT * FROM [dbo].[FeaturesOrder] 
order by 
   DisplayOrder
   , replace(replace(FeatureName,'Competitor',''),'NXP','')
   , replace(replace(FeatureName,'Competitor',1),'NXP',2)

1 Like

thanks solved