Click here to Skip to main content
15,867,835 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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 statement :

SQL
USE [ExtractReports]
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 :

SQL
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 have tried:

SQL
SELECT   FeatureName,displayorder 
  FROM   [ExtractReports].[dbo].[FeaturesOrder]  with(nolock)
  group by FeatureName,displayorder
  ORDER BY  displayorder ASC,FeatureName asc
Posted
Updated 21-Apr-21 6:09am

1 solution

Essentially, you can't. You need some additional indicators.

For example, split out the "Competitor" or "NXP" away from the actual FeatureName
SQL
CREATE TABLE [#FeaturesOrder](
[CompOrNXP] [nvarchar](10) NOT NULL,
[FeatureName] [nvarchar](511) NULL,
[DisplayOrder] [int] NULL
) ON [PRIMARY]
 GO
 INSERT [#FeaturesOrder] ([CompOrNXP], [FeatureName], [DisplayOrder]) VALUES 
 (N'Competitor', N'Accelerometers Type', 3)
,(N'Competitor', N'Battery Type', 3)
,(N'Competitor', N'Function', 3)
Then you can use an appropriate ORDER BY and just tack the Competitor/NXP back onto the Feature name. E.g.
SQL
SELECT   concat([CompOrNXP], ' ', FeatureName),displayorder
 FROM   [#FeaturesOrder]  with(nolock)
 ORDER BY  displayorder, FeatureName, [CompOrNXP]
This way you will get whatever the "thing" is in Competitor then NXP order.

I still cannot produce the results you are expecting because there is nothing to indicate why "Battery Type" should come before "Amplifier Type" so perhaps you need another column to differentiate these, or change the "DisplayOrder" value for that from 3 to 4.

Note that I have taken out the
SQL
group by FeatureName,displayorder
You are not using any aggregate functions so there is absolutely no need for the grouping.
 
Share this answer
 
Comments
RedDk 21-Apr-21 12:55pm    
Just to be clear then, SELECT CONCAT([CompOrNXP],' ', [FeatureName]) As [bugBigger], [DisplayOrder] is still MySQL (something that this altered question used to be before the poster created the above snafu by reigniting the controversy over editing Qs in QA) ... ?
CHill60 21-Apr-21 13:37pm    
I'm confused... question doesn't appear to have been altered and OP has several questions tagged SQL/T-SQL and none tagged MySQL.
But, yes, CONCAT is also MySQL.
I'm unaware of any such controversy.
Not sure of your point.
RedDk 21-Apr-21 14:15pm    
Thanks

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900