Order by Items Must Appear in the Select List If Select Distinct Is Specified

ORDER BY items must appear in the select list if SELECT DISTINCT is specified

Try this:

ORDER BY 1, 2

OR

ORDER BY rsc.RadioServiceCodeId, rsc.RadioServiceCode + ' - ' + rsc.RadioService

Reason for - ORDER BY items must appear in the select list if SELECT DISTINCT is specified

A query with SELECT DISTINCT can be rewritten using GROUP BY. So the query:

SELECT DISTINCT city
FROM HR.Employees
WHERE country = N'USA' AND region = N'WA' ;

is equivalent to:

SELECT city
FROM HR.Employees
WHERE country = N'USA' AND region = N'WA'
GROUP BY city ;

and you can't use ORDER BY birthdate here either. The reason is the same for both queries. There may be many (more than one) rows with same city but different birthdate. Which one should be used for the ordering (if it was allowed?)

You can however use aggregate functions with a GROUP BY query:

SELECT city
FROM HR.Employees
WHERE country = N'USA' AND region = N'WA'
GROUP BY city 
ORDER BY MIN(birthdate) ;               -- or MAX(birthdate)

ORDER BY items must appear in the select list if SELECT DISTINCT when selecting from aliased subquery

You would need to put the collation in the `select:

SELECT DISTINCT TOP 100 T.Number, T.LastName COLLATE Latin1_General_CI_AI, T.FirstName

This is a rather insignificant change unless you have a case sensitive collation and you want to see the duplicates.

If so, then use GROUP BY and LOWER() (or UPPER()):

SELECT TOP 100 T.Number, T.LastName, T.FirstName
FROM (SELECT TOP 1700 -- 100 * (mean avg # site per PI + 3sd)
             PI.Number,  PI.LastName, PI.FirstName
      FROM Sites S INNER JOIN  
           PI
           ON S.PI = PI.Number INNER JOIN 
           Protocol P
           ON PI.ProtocolNumber = P.ProtocolNumberINNER JOIN 
           ProductMaster PM 
           ON PA.productcode = PM.productcode
      WHERE -- predicates
     ) T 
GROUP BY T.Number, T.LastName T.FirstName 
ORDER BY MAX(lower(T.LastName));

Error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified

Rather than use SELECT DISTINCT, use GROUP BY (to match the sort order specified in the comments):

SELECT 
    [Theme].[Name], [ThemeType].[Type] 
FROM 
    [Theme] 
LEFT OUTER JOIN 
    [ThemeType] ON [Theme].[ThemeTypeId] = [ThemeType].[PK_ThemeType]
JOIN 
    [ProductTheme] ON [ProductTheme].[ThemeId]=[Theme].[PK_Theme]
WHERE 
    ProductTheme.ProductID LIKE '%' 
    AND ProductTheme.ThemeId = Theme.PK_Theme 
    AND COALESCE([THEME].[THEMETYPEID], 'null') LIKE '%[0-9]%' 
GROUP BY
    [Theme].[Name], [ThemeType].[Type] 
ORDER BY 
    CASE WHEN [ThemeType].[Type] IS NULL 
           THEN 0 
           ELSE 1 
    END, [Theme].[Name]

ORDER BY items must appear in the select list if SELECT DISTINCT is specified?

Never use commas in the FROM clause. Always use proper, explicit, standard, readable JOIN syntax.

you can fix the problem using GROUP BY. This allows you to use aggregation functions on columns in the ORDER BY:

select TOP (1) Error
from tablename v join
     tablename j 
     on v.columname = j.columnname1
group by Error
order by max(v.columnname), max(j.columnname1);

In my experience, this is normally used for date/time columns to order something by the most recent time it appeared.

When Select distinct From Features give Error ORDER BY items must appear in the select list?

Instead of using DISTINCT, use a GROUP BY clause. This allows you to ORDER BY an aggregated missing column. Principle

SELECT A, B
GROUP BY A, B
ORDER BY MAX(C)

Or simply do what the error message suggest: Include DisplayOrder in the select list.

SELECT distinct DisplayOrder, ', ' +  case when A.splitFlag = 1 then ''''+A.DKFeatureName ...

The problem is that DISTINCT may discard some rows. So if DisplayOrder is different within these otherwise equal rows, which one of them should be used for sorting? With GROUP BY, you can specify which one. In my example it would be MAX(DisplayOrder), i.e., the largest within otherwise distinct rows. It also could be MIN() or AVG() or whatever.

ORDER BY items must appear in the select list if SELECT DISTINCT is specified when using Calculated ORDER BY

You need to add your order by items into your select statement. i.e.

SELECT DISTINCT TOP 40 LocatorID, DisplayAddress, 
(Longitude + 0.537143) * (Longitude + 0.537143) + (Latitude - 51.253741) * (Latitude - 51.253741) as MyPosition 
FROM [UKStreetsAndPlaces].[dbo].[OS_Locator] 
WHERE (SearchAddress LIKE 'Bal%') 
ORDER BY (Longitude + 0.537143) * (Longitude + 0.537143) + (Latitude - 51.253741) * (Latitude - 51.253741) ASC

---

Related Topics