error display ORDER BY items must appear in the select list if SELECT DISTINCT

Question

Post reply

error display ORDER BY items must appear in the select list if SELECT DISTINCT

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

January 5, 2021 at 11:31 pm

#3827860

I get error display ORDER BY items must appear in the select list if SELECT DISTINCT

why this error display and how to solve issue

create table #allfeatures
(
FeatureName  nvarchar(100),
DisplayOrder Int
)


insert into #allfeatures(FeatureName,DisplayOrder)
values
('Competitor Supply Current',7),
('Competitor Minimum Supply Voltage',5),
('Competitor Maximum Supply Voltage',4),
('Competitor Minimum Operating Temperature',8),
('Competitor Maximum Operating Temperature',9),
('Competitor Operating Frequency',6),
('Competitor Applications',3),
('NXP Supply Current',7),
('NXP Minimum Supply Voltage',5),
('NXP Maximum Supply Voltage',4),
('NXP Minimum Operating Temperature',8),
('NXP Maximum Operating Temperature',9),
('NXP Operating Frequency',6),
('NXP Applications',3),
('Competitor Automotive',1),
('NXP Automotive',1),
('Competitor SecurityApproval',1),
('NXP SecurityApproval',1),
('Competitor Normalized Package Name',2),
('NXP Normalized Package Name',2),
('Competitor ZTemperatureGrade',10),
('NXP ZTemperatureGrade',10)

DECLARE @result NVARCHAR(MAX)
SELECT  @result = ( SELECT  STUFF(( SELECT   ',[' +  FeatureName + ']' FROM #allfeatures
group by FeatureName,displayorder
ORDER BY  (CASE WHEN displayorder IS NULL THEN 1 ELSE 0 END) asc,displayorder,FeatureName asc 
FOR
XML PATH('')
), 1, 1, '') AS [Output]  )
select @result

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80325 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

cyrusbaratt SSC-Addicted Points: 417 More actions · Answer 2

The following must be in your select statement:

(CASE WHEN displayorder IS NULL THEN 1 ELSE 0 END)

You will require an additional T-SQL variable.

Cheers

DBASupport