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

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  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 :

  FeatureNamedisplayorder
  Competitor Automotive1
  NXP Automotive1
  Competitor Normalized Package Name2
  NXP Normalized Package Name2
  Competitor Accelerometers Type3
  NXP Accelerometers Type3
  Competitor Battery Type3
  NXP Battery Type3
  Competitor Function3
  NXP Function3
  Competitor Multiplexer And Demultiplexer3
  NXP Multiplexer And Demultiplexer3
  Competitor Type3
  NXP Type3
  Competitor Multi-Demultiplexer Circuit3
  NXP Multi-Demultiplexer Circuit3
  Competitor Amplifier Type3
  NXP Amplifier Type3
  Competitor Diode Type3
  NXP Diode Type3
  Competitor Output Type3
  NXP Output Type3
  

   

• Mark Cowne

  One Orange Chip

  Points: 26952

  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