CODEWITHSUNDEEP
sqlt-sql
AlexGH
AlexGH

Reputation: 2805

Order by in both sides of union

I need to order by city both sides of the union query:

select * from( select top(1)city, len(city) from Station    
where len(city) = (select min(len(d.city)) from Station d ) order by city ) as a
union all select * from  (select top(1) city, LEN(city) from Station 
 where len(city) = (select max(len(f.city)) from station f) order by city ) as b

I need to order both sides because if I have two cities with the same length then I need to get the first in alphabetical order, and if I put the order by at the end of the whole query it will not do what I want, if you have another way to do this I will appreciate suggestions..

When I run this query I'm getting this error:

Msg 8155, Level 16, State 2, Line 3
No column name was specified for column 2 of 'a'.
Msg 8155, Level 16, State 2, Line 4
No column name was specified for column 2 of 'b'.

What should I do?

Upvotes: 0

Views: 137

Answers (3)

paparazzo
paparazzo

Reputation: 45096

select * from ( select top(1) city, len(city) as 'length' 
                from Station    
                order by len(city) asc, city ) as a
union all 
select * from ( select top(1) city, len(city) as 'length' 
                from Station    
                order by len(city) desc, city ) as b

Upvotes: 1

TheGameiswar
TheGameiswar

Reputation: 28890

Specify column first for len(city)

like len(city) as lencity

and don't order by in both statements,it doesn't make any difference..Its the final order by that matters.. and order by is not required in aggregates

So your statement can be..

select * from( select top(1)city, len(city) as lencity
 from Station    
where len(city) = (select min(len(d.city)) from Station d ) ) as a
union all 
select * from  (select top(1) city, LEN(city) as lencity from Station 
 where len(city) = (select max(len(f.city)) from station f)  ) as b
order by lencity

Upvotes: 0

AlexGH
AlexGH

Reputation: 2805

Fixed

select * from( select top(1)city, len(city) as 'length' from Station    
where len(city) = (select min(len(d.city)) from Station d ) order by city ) as a
union all select * from  (select top(1) city, LEN(city) as 'length' from Station 
 where len(city) = (select max(len(f.city)) from station f) order by city ) as b

Upvotes: 0

Related Questions