I was working in a scenario where I need
to get distinct names order by some other column which was not a part of Select
Query. Below I will show you how to fix this error if you will stuck with the
same error.
The error message was as below.
Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select
list if SELECT DISTINCT is specified.
Below I will create a test environment to
create the error and in next step I will show how to fix this above error.
--test table script
create table #TestOrder
(id int identity(1,1),OrderName varchar(100),Period varchar(100),Month int)
Insert into #TestOrder values('TV','Jan15',1)
Insert into #TestOrder values('WashingMachine','Feb15',2)
Insert into #TestOrder values('TV','Mar15',3)
Insert into #TestOrder values('Refrigrator','Apr15',4)
--Need Distinct of below two since we
don't know the month
Insert into #TestOrder values('TV','2015',13)
Insert into #TestOrder values('TV','2015',14)
If you will analyse the above table you
can notice that we have three main Column orderName Period and Month column.
OrderName- Shows the order Name
Period- MonthName along with the Year when the
order was made
Month- No. of Month for the order for eg-for
Jan=1,for Feb=2 and so on…
Now if you will see the last two records the Period
is 2015 and Month is 13 and 14 which means for these two records it was not
sure for which month this Product was ordered so they made period entry as 2015
and put a random entry as 13 and 14 for the Month.
Requirement-So, the Requirement was
we wants to see a single column in the output which will be distinct and will
be concatenation of Order Name and period order by Month. Since for the last
two records the order is same so we will take distinct of it and keep it them in the top as below screenshot
I tried the below
query to take distinct of order and period name order by month I got the above error.
select distinct OrderName +'_'+Period as OrderPeriod from #TestOrder
order by month
Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select
list if SELECT DISTINCT is specified.
Solution- So
below code will give you distinct name order by a column Month which is not a
part of Select query.
select OrderName +'_'+Period as Order_Period from #TestOrder
group by OrderName +'_'+Period
order by min(Month)
Output as Required.
Note: Since we
are using aggregate function in the order by so you must need to write group by
before order by clause other wise it will throw the error. for eg as below
select OrderName +'_'+Period as Order_Period from #TestOrder
--group by OrderName +'_'+Period
order by min(Month)
Msg 8120, Level 16, State 1, Line 1
Column '#TestOrder.OrderName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
So the above query will solve your issue if in case you are stuck
with some similar scenario where you want distinct record order by a column
which is not a part of Select query.
If you have any other issue regarding SQL & BI where you are
stuck write to me in the comment box I will try to help you out.
