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

  • Question

  • User696604810 posted

    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

    Tuesday, January 5, 2021 11:32 PM

All replies

  • User-939850651 posted

    Hi ahmedbarbary,

    When distinct is used, the distinct columns will have a sorting feature, and it will be sorted according to the distinct columns. order by can change the sorting of distinct, so as to achieve the sorting effect you want, instead of the sorting effect of distinct.

    How distinct is sorted, and how it is repeated, you could refer to SQL execution plan.

    In summary, this is a question of the order of execution. When there is no distinct keyword, the order by keyword is executed first, and the select keyword is executed later. With the distinct keyword, the selcet distinct keyword is executed first, and the order by keyword is executed later. After distinct is executed, there are no columns, then order by will not be able to complete the sorting feature.

    You can also find many cases of the same problem, and you could also refer to the relevant explanations and solutions.

    Best regards,

    Xudong Peng

    Wednesday, January 6, 2021 8:49 AM