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

Hi nedash,

To avoid the error specifying TOP would be one possibility.

ALTER PROCEDURE [dbo].[Select_DocTopicMain]
AS
BEGIN
	SET NOCOUNT ON
	SELECT DISTINCT Topic,TopicId 
	FROM (
			SELECT TOP 100 PERCENT Topic,TopicId,Preference 
			FROM Documentry_Info 
			WHERE Topic IS NOT NULL 
			ORDER BY Preference ASC
		) a
END

hi dharmendr

I changed code but it doesn't order by preference

I mean this line

ORDER BY Preference ASC

Hi nedash,

Refer the below sample query.

SQL

SELECT * FROM CustomersWithOutIdentity
SELECT CustomerId,Name
FROM (
		SELECT DISTINCT CustomerId,Name,Country,ROW_NUMBER() OVER(ORDER BY Country ASC) CountryOrder 
		FROM (SELECT TOP 100 PERCENT CustomerId,Name,Country FROM CustomersWithOutIdentity WHERE CustomerId IS NOT NULL) b
     )a ORDER BY CountryOrder

Input

Output

So you need to change you query like below.

SELECT Topic,TopicId
FROM (
		SELECT DISTINCT TopicId,Topic,Preference,ROW_NUMBER() OVER(ORDER BY Preference ASC) PreferenceOrder 
		FROM (SELECT TOP 100 PERCENT TopicId,Topic,Preference FROM Documentry_Info WHERE Topic IS NOT NULL) b
     )a ORDER BY PreferenceOrder

hi dharmendr

I change code but now distinct doesn't work

see below table:

here I want when run SP it shows Documentry one time but here it will shows Documentry twice like table...

best regards

neda

Hi nedash,

Distinct will not work since you are selecting multiple column. It will work if your record is same in each column, if there is a different with a character then it will give you both record.