ORDER BY items must appear in the select list if S

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

ORDER BY items must appear in the select list if S

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-23 : 02:19:16

Hi.
If i remove "distinct" it works fine.
I'm a little lost.Where exactly is the problem?
Thanks.

SELECT distinct owners.id,owners.name,owners.lastname,owners.phone,owners.mail,
owners.mobilephone,owners.address  
FROM         Doctors INNER JOIN                      
                      Owners ON doctors.id = Owners.doctoridunjoined
where doctors.username=lower(@doctorusername) and owners.doctoridunjoined = doctors.id
order by 
CASE WHEN @orderbyc=1 THEN owners.name end asc,
case when @orderbyc=2 Then owners.lastname end asc

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-23 : 02:44:07

Sorry the complete title is :
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-23 : 02:46:48

quote:
Originally posted by sapator

Sorry the complete title is :
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Could you please show us the sample data and expected output.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-23 : 02:51:05

What do you mean?
The rows are correct, if that's what you mean.
The expected output is the select in order by owners.name (if 1) or owners.lastname(if 2).
If i remove distinct it will work fine.I suspect that distinct and case(s) in order by don't mix good.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-23 : 02:53:39

Anyway i did this with another way, so that i don't need distinct, but it's good to know if there is a solution.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-23 : 02:56:13

Try


SELECT owners.id,owners.name,owners.lastname,owners.phone,owners.mail,
owners.mobilephone,owners.address  
FROM         Doctors INNER JOIN                      
                      Owners ON doctors.id = Owners.doctoridunjoined
where doctors.username=lower(@doctorusername) and owners.doctoridunjoined = doctors.id
GROUP BY owners.id,owners.name,owners.lastname,owners.phone,owners.mail,
owners.mobilephone,owners.address  
order by 
CASE WHEN @orderbyc=1 THEN owners.name end asc,
case when @orderbyc=2 Then owners.lastname end asc

Madhivanan

Failing to plan is Planning to fail

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-23 : 02:58:47

quote:

Originally posted by madhivanan

Try


SELECT owners.id,owners.name,owners.lastname,owners.phone,owners.mail,
owners.mobilephone,owners.address  
FROM         Doctors INNER JOIN                      
                      Owners ON doctors.id = Owners.doctoridunjoined
where doctors.username=lower(@doctorusername) and owners.doctoridunjoined = doctors.id
GROUP BY owners.id,owners.name,owners.lastname,owners.phone,owners.mail,
owners.mobilephone,owners.address  
order by 
CASE WHEN @orderbyc=1 THEN owners.name end asc,
case when @orderbyc=2 Then owners.lastname end asc

Madhivanan

Failing to plan is Planning to fail

hi madhi,
i wonder,Some days before you suggested me not to use case statements in order by which is not good pratice.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-23 : 03:02:48

Hi.
Trying it on query, embedded both Idera and harron solutions but it still shows ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

declare @doctorusername nvarchar(256)
declare @orderbyc bigint
set @doctorusername='uns'
set  @orderbyc=1
SELECT distinct owners.id,owners.name,owners.lastname,owners.phone,owners.mail,
owners.mobilephone,owners.address  
FROM         Doctors INNER JOIN                      
                      Owners ON doctors.id = Owners.doctoridunjoined
where doctors.username=lower(@doctorusername) and owners.doctoridunjoined = doctors.id
and (@orderbyc=1 or @orderbyc=2)
GROUP BY owners.id,owners.name,owners.lastname,owners.phone,owners.mail,
owners.mobilephone,owners.address  
order by 
CASE WHEN @orderbyc=1 THEN owners.name end asc,
case when @orderbyc=2 Then owners.lastname end asc

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-23 : 03:04:41

sorry madhivanan solution :)

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-23 : 03:05:41

Anyway , do you have another way to do and order by for to situations without case?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-23 : 03:07:23

quote:

Originally posted by sapator

Hi.
Trying it on query, embedded both Idera and harron solutions but it still shows ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

declare @doctorusername nvarchar(256)
declare @orderbyc bigint
set @doctorusername='uns'
set  @orderbyc=1
SELECT distinct owners.id,owners.name,owners.lastname,owners.phone,owners.mail,
owners.mobilephone,owners.address  
FROM         Doctors INNER JOIN                      
                      Owners ON doctors.id = Owners.doctoridunjoined
where doctors.username=lower(@doctorusername) and owners.doctoridunjoined = doctors.id
and (@orderbyc=1 or @orderbyc=2)
GROUP BY owners.id,owners.name,owners.lastname,owners.phone,owners.mail,
owners.mobilephone,owners.address  
order by 
CASE WHEN @orderbyc=1 THEN owners.name end asc,
case when @orderbyc=2 Then owners.lastname end asc

Did you see my code?. I didn't use DISTINCT

Madhivanan

Failing to plan is Planning to fail

Sachin.Nand

2937 Posts

Posted - 2010-03-23 : 03:08:58

No it wont work.You will have to add the case statements of the order by clause in the select part to.
try this


declare @ord int
select @ord=1
declare @tbl as table(id int,id1 int)
insert into @tbl
select 1,2 union all
select 4,1 union all
select 3,8 

select distinct id,id1,case @ord when 1 then id else id1 end from @tbl
order by case @ord when 1 then id else id1 end

select distinct id,id1 from @tbl
order by case @ord when 1 then id else id1 end

The first query will work the second one will give you an error

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-23 : 03:09:18

quote:

Originally posted by haroon2k9

quote:

Originally posted by madhivanan

Try


SELECT owners.id,owners.name,owners.lastname,owners.phone,owners.mail,
owners.mobilephone,owners.address  
FROM         Doctors INNER JOIN                      
                      Owners ON doctors.id = Owners.doctoridunjoined
where doctors.username=lower(@doctorusername) and owners.doctoridunjoined = doctors.id
GROUP BY owners.id,owners.name,owners.lastname,owners.phone,owners.mail,
owners.mobilephone,owners.address  
order by 
CASE WHEN @orderbyc=1 THEN owners.name end asc,
case when @orderbyc=2 Then owners.lastname end asc

Madhivanan

Failing to plan is Planning to fail

hi madhi,
i wonder,Some days before you suggested me not to use case statements in order by which is not good pratice.

It was in WHERE clause.

Madhivanan

Failing to plan is Planning to fail

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-23 : 03:12:30

quote:
Originally posted by sapator

Anyway , do you have another way to do and order by for to situations without case?

try this


SELECT owners.id,owners.name,owners.lastname,owners.phone,owners.mail,
owners.mobilephone,owners.address ,ROW_NUMBER()over(partition by owners.id  order by 
 CASE WHEN @orderbyc=1 THEN owners.name asc else owners.lastname asc end)as seq
FROM         Doctors INNER JOIN                      
                      Owners ON doctors.id = Owners.doctoridunjoined
where doctors.username=lower(@doctorusername) and owners.doctoridunjoined = doctors.id and seq=1

Sachin.Nand

2937 Posts

Posted - 2010-03-23 : 03:17:56

Well I would go for IF conditions.


If @orderbyc=1
your query order by owners.name 

If @orderbyc!=1
your query order by owners.lastname

PBUH

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-23 : 03:24:56

Hi.Haroon i think your code is to complicated for me to use in my sp's.

Idera, i think i made it work.Have a look, but it's also a little troublesome.Anyway i marked it down in a case that i will need distinct in my select.
Thanks.

 declare @doctorusername nvarchar(256)
declare @orderbyc bigint
set @doctorusername='ddv'
set  @orderbyc=2
SELECT distinct owners.id,owners.name,owners.lastname,owners.phone,owners.mail,
owners.mobilephone,owners.address,CASE  @orderbyc WHEN 1 THEN owners.name end,
case @orderbyc when 2 Then owners.lastname end 
FROM         Doctors INNER JOIN                      
                      Owners ON doctors.id = Owners.doctoridunjoined
where doctors.username=lower(@doctorusername) and owners.doctoridunjoined = doctors.id

order by 
CASE WHEN @orderbyc=1 THEN owners.name end asc,
case when @orderbyc=2 Then owners.lastname end asc

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-23 : 03:27:17

Hmm.If's.....
Well for 2-3 cases it's ok but i had a sp's with 10 cases.Also,theoretically if's are slower than case's because they will have to pass all the conditions to find a much.

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-23 : 03:30:51

<<too complicated>>

can you try this and see



if @orderbyc=1
begin

SELECT owners.id,owners.name,owners.lastname,owners.phone,owners.mail,
owners.mobilephone,owners.address ,ROW_NUMBER()over(partition by owners.id order by  owners.name asc)as seq
FROM         Doctors INNER JOIN                      
                      Owners ON doctors.id = Owners.doctoridunjoined
where doctors.username=lower(@doctorusername) and owners.doctoridunjoined = doctors.id and seq=1
end
else
begin
SELECT owners.id,owners.name,owners.lastname,owners.phone,owners.mail,
owners.mobilephone,owners.address ,ROW_NUMBER()over(partition by owners.id order by  owners.lastname  asc)as seq
FROM         Doctors INNER JOIN                      
                      Owners ON doctors.id = Owners.doctoridunjoined
where doctors.username=lower(@doctorusername) and owners.doctoridunjoined = doctors.id and seq=1
end

Sachin.Nand

2937 Posts

Posted - 2010-03-23 : 03:36:54

Yes if the conditions are more if's are not a very viable solution.Just because u posted 2 conditions I suggested u use if's.Also ur query is not right.It shud be this way


if @orderbyc=1
begin
select * from
(
SELECT owners.id,owners.name,owners.lastname,owners.phone,owners.mail,
owners.mobilephone,owners.address ,ROW_NUMBER()over(partition by owners.id order by  owners.name asc)as seq
FROM         Doctors INNER JOIN                      
                      Owners ON doctors.id = Owners.doctoridunjoined
where doctors.username=lower(@doctorusername) and owners.doctoridunjoined = doctors.id 
)T where seq=1

PBUH

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-23 : 03:37:10

I said complicated cuz i'm not very familiar with "ROW_NUMBER()over(partition by".
Also seq=1?You don't declare it somewhere.Give's "Invalid column name 'seq'.
Also as i've said 2-3 if's is ok but if i had 10 conditions then i've needed 10 times your code+ if is slower.
I think the nested cases embedded in one statement will do better.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-03-23 : 03:40:37

Yes i didn't think we will use if statements, that's why i didn't specify it.
Also the last code is killing me.I cannot make it work.
What is this?Where is the group by?
Sorry i'm not an sql expert.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources