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](
[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 :
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
Try this
SELECT FeatureName,displayorder
FROM [ExtractReports].[dbo].[FeaturesOrder] with(nolock)
group by FeatureName,displayorder
ORDER BY displayorder ASC,SUBSTRING(FeatureName,CHARINDEX(' ',FeatureName),511) asc,FeatureName
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply