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

Question

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

what i try

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

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 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 :

as below :

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

Answer 1

This seems to work:

SELECT FeatureName, displayorder 
FROM FeaturesOrder with(nolock)
ORDER BY
    displayorder, 
    case 
        when FeatureName like N'Competitor %' then substring(FeatureName, len(N'Competitor ') + 1, len(FeatureName))
        when FeatureName like N'NXP %' then substring(FeatureName, len(N'NXP ') + 1, len(FeatureName))
    end,
    FeatureName

Answer 2

Hi @ahmed salah ,

Please also refer below:

SELECT FeatureName, displayorder   
 FROM [dbo].[FeaturesOrder]  with(nolock)  
 ORDER BY displayorder  
 ,SUBSTRING(FeatureName,CHARINDEX(' ',FeatureName)+1,100),FeatureName  

OR

SELECT [FeatureName], [DisplayOrder] FROM (   
SELECT *, ROW_NUMBER() OVER (ORDER BY displayorder,FeatureName) RN  
FROM [dbo].[FeaturesOrder] with(nolock)  
WHERE FeatureName LIKE 'Competitor%'  
UNION   
SELECT *, ROW_NUMBER() OVER (ORDER BY displayorder,FeatureName) RN  
FROM [dbo].[FeaturesOrder] with(nolock)  
WHERE FeatureName LIKE 'NXP%') A  
ORDER BY DisplayOrder,RN  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.