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

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

The scenario where i faced this issue when in a  query i want  to sort Data first on year then by month Name(like jan,feb then march and so on.... ) not alphabetically and table was having duplicate data. 

So Below is the solution with sample Table

-- sample table script  with duplicate records

CREATE TABLE [dbo].[Employee1_Errors](

[Empid] [int] IDENTITY(1,1) NOT NULL,

[EmpName] [nvarchar](50) NULL,

[EmpSalary] [float] NULL,

[EmpDob] [datetime] NULL

) ON [PRIMARY]

GO

SET IDENTITY_INSERT [dbo].[Employee1_Errors] ON

INSERT [dbo].[Employee1_Errors] ([Empid], [EmpName], [EmpSalary], [EmpDob]) VALUES (1, N'vikas', 2000, CAST(0x0000A1EC01250538 AS DateTime))

INSERT [dbo].[Employee1_Errors] ([Empid], [EmpName], [EmpSalary], [EmpDob]) VALUES (2, N'vikas1', 1000, CAST(0x0000A18801253815 AS DateTime))

INSERT [dbo].[Employee1_Errors] ([Empid], [EmpName], [EmpSalary], [EmpDob]) VALUES (3, N'vikas2', 3000, CAST(0x0000A12401253815 AS DateTime))

INSERT [dbo].[Employee1_Errors] ([Empid], [EmpName], [EmpSalary], [EmpDob]) VALUES (4, N'vikas2', 3000, CAST(0x0000A0C001258712 AS DateTime))

INSERT [dbo].[Employee1_Errors] ([Empid], [EmpName], [EmpSalary], [EmpDob]) VALUES (5, N'vikas2', 3000, CAST(0x0000A05C01258712 AS DateTime))

INSERT [dbo].[Employee1_Errors] ([Empid], [EmpName], [EmpSalary], [EmpDob]) VALUES (6, N'vikas3', 3000, CAST(0x0000A0C00125A339 AS DateTime))

INSERT [dbo].[Employee1_Errors] ([Empid], [EmpName], [EmpSalary], [EmpDob]) VALUES (7, N'vikas4', 3000, CAST(0x0000A05C0125A339 AS DateTime))

INSERT [dbo].[Employee1_Errors] ([Empid], [EmpName], [EmpSalary], [EmpDob]) VALUES (8, N'vikas', 2000, CAST(0x0000A1EC01250538 AS DateTime))

INSERT [dbo].[Employee1_Errors] ([Empid], [EmpName], [EmpSalary], [EmpDob]) VALUES (9, N'vikas1', 1000, CAST(0x0000A18801253815 AS DateTime))

INSERT [dbo].[Employee1_Errors] ([Empid], [EmpName], [EmpSalary], [EmpDob]) VALUES (10, N'vikas2', 3000, CAST(0x0000A12401253815 AS DateTime))

INSERT [dbo].[Employee1_Errors] ([Empid], [EmpName], [EmpSalary], [EmpDob]) VALUES (11, N'vikas2', 3000, CAST(0x0000A0C001258712 AS DateTime))

INSERT [dbo].[Employee1_Errors] ([Empid], [EmpName], [EmpSalary], [EmpDob]) VALUES (12, N'vikas2', 3000, CAST(0x0000A05C01258712 AS DateTime))

INSERT [dbo].[Employee1_Errors] ([Empid], [EmpName], [EmpSalary], [EmpDob]) VALUES (13, N'vikas3', 3000, CAST(0x0000A0C00125A339 AS DateTime))

INSERT [dbo].[Employee1_Errors] ([Empid], [EmpName], [EmpSalary], [EmpDob]) VALUES (14, N'vikas4', 3000, CAST(0x0000A05C0125A339 AS DateTime))

SET IDENTITY_INSERT [dbo].[Employee1_Errors] OFF

Our requirement is distinct data with sort by year then months.

IF we write this simple query it will sort by year and month but month name in alphabatical order 

select distinct empname,empsalary, YEAR(empdob)as Dobyear,DATENAME(mm,empdob) as DOBMonth from Employee1_Errors 

order by Dobyear,DOBMonth .

Screenshot distinct data sory by year and month name alphabatically.

the above query  has given us distinct record  order by year but monthname  in A to Z not like jan ,feb and so on........

if duplicate data was not there we can achieve the result by the below query

select  Empid,empname,empsalary, YEAR(empdob)as Dobyear,DATENAME(mm,empdob) as DOBMonth from Employee1_Errors 

order by Dobyear,datepart(mm,DATENAME(mm,empdob)+'1900')

Screenshot of avove query Sort by year and Month but having Duplicate Records.

if we will add distinct in the above query to remove duplicate data it will throw an error.

select distinct Empid,empname,empsalary, YEAR(empdob)as Dobyear,DATENAME(mm,empdob) as DOBMonth from Employee1_Errors 

order by Dobyear,datepart(mm,DATENAME(mm,empdob)+'1900')

Msg 145, Level 15, State 1, Line 2

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

SO in order to achive the result and  remove this error we can use CTE(comman table expression or Derived table)

Solution with CTE

with cte as

(select distinct empname,empsalary, YEAR(empdob)as Dobyear,DATENAME(mm,empdob) as DOBMonth from Employee1_Errors )

select * from cte order by Dobyear,datepart(mm, DOBMonth+'1900' )

Solution with Derived Table

select * from 

(

select distinct empname,empsalary, YEAR(empdob)as Dobyear,DATENAME(mm,empdob) as DOBMonth from Employee1_Errors

 )X

 order by Dobyear,datepart(mm, DOBMonth+'1900' )

 The above both query will give remove duplicate data and sorting them first by year then by month names.

Screenshot of Distinct Data sort by Year and MonthName.