Custom Sort in ORDER BY clause in SQL Server

ahmedsa
 
on Apr 14, 2021 10:47 PM
671 Views

How to Display Feature Name of Competitor 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 competitor 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 competitor feature come first then nxp second for same display order and this wrong

so wrong is features will display for same display order as :

competitor function competitor type

nxp function nxp type

correct is features will display for same display order as :

competitor function nxp function

competitor 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

 

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
Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Apr 14, 2021 10:47 PM

I will get back to you soon.

dharmendr
 
on Apr 15, 2021 03:48 AM
on Apr 15, 2021 03:58 AM

Hi ahmedsa,

Refer below query.

SQL

SELECT FeatureName, displayorder
FROM [FeaturesOrder] WITH(NOLOCK)
GROUP BY FeatureName, displayorder
ORDER BY displayorder ASC, REVERSE(FeatureName) DESC