How to use distinct insteade of using group by ?

Question

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

sample data

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

Answer 1

Please try to add all the columns selected in select to the order by list.

I found a similar problem, including the replacement of distinct and group by, and the error you mentioned:
ORDER BY items must appear in the select list if SELECT DISTINCT is specified

Echo

Answer 2

i try as below
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

really i don't need display order on select so what i do to solve issue

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

Answer 3

Your original query returns a comma-separated list of features, which does not include duplicates for provided data. Give the expected string if the result is not good.

Do you want to exclude possible duplicates, ignoring the unneeded DisplayOrder? Then consider the next query:

set @result = STUFF(( SELECT  ',[' +  FeatureName + ']'     
    FROM ( select distinct FeatureName from #allfeatures ) d
    ORDER BY FeatureName asc                                 
    FOR
    XML PATH('')
    ), 1, 1, '')

Answer 4

Try:

    DECLARE @result NVARCHAR(MAX)
    SELECT @result = ( SELECT STUFF(( SELECT distinct ',[' + FeatureName + ']' f
    FROM #allfeatures
    ORDER BY f
    FOR
    XML PATH('')
    ), 1, 1, '') AS [Output] )
    select @result

Please provide the desired output.

Regards
Echo

Answer 5

Here a solution without both DISTINCT and GROUP BY clauses.

It is based on XML and XQuery, and using the distinct-values() XQuery function to achieve the desired outcome.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, FeatureName  nvarchar(100), DisplayOrder Int);
INSERT INTO @tbl(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);
-- DDL and sample data population, end

SELECT (
 SELECT FeatureName AS [r] FROM @tbl
 FOR XML PATH(''), TYPE, ROOT('root')
).query('
for $x in distinct-values(/root/r/text())
let $token := data(concat("[",$x,"]"))
return 
if ($x eq (distinct-values(/root/r/text())[last()])[1]) then $token
      else concat($token, ",")
').value('.', 'NVARCHAR(MAX)') AS Result;