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

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3070

  More actions

  April 15, 2021 at 3:53 am

  Go to Answer

  #3871372

  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
  

   

• Mark Cowne

  One Orange Chip

  Points: 26865

  More actions

  April 15, 2021 at 8:36 am

  Answer

  #3871473

  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/61537