How to use distinct instead of using group by?

Transact-SQL
, ,
1

How to use distinct instead of using group by ?

I work on SQL server 2012 I need to replace group by with distinct

so How to modify next statement to use distinct instead of group by

 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]  )
2

please provide sample data and the desired output

3 
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
4 
DECLARE @result NVARCHAR(MAX)
SELECT  @result = ( SELECT  STUFF(( SELECT distinct  ',[' +  FeatureName + ']' 	FROM #allfeatures
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

i get error
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

5

i am not getting any error

image
image1658×183 15.1 KB

6

so how to solve it

7

Why do you need to use distinct? Can you use this?

select FeatureName , min(DisplayOrder) as DisplayOrder
FROM #allfeatures
group by FeatureName
order by 2

8

what is the result you want? just sayin "how to use distinct instead of using group by" does not tell us what the final result you want is and why

9

You are selecting a single column - FeatureName - but trying to order by 3 different columns. You cannot order by more columns than are listed in the select statement if you use DISTINCT.

Before attempting to concatenate the results into a single column - you need to figure out the query that returns those results in the order you need them. Once you have that query then you can work out how to concatenate the results.

In your sample data - which one do you want to keep, the one with the lowest display order or the one with the highest display order?

('NXP Minimum Supply Voltage', 5),
('NXP Maximum Supply Voltage', 4),