SQL Union problems when using Order By

Question

Here is the query I am using:

select top 1 'Return To IPACS' as name, 'http://domain:88' as link 
union 
select name,link 
from jrm_intranetlinks l 
inner join jrm_intranetpermissions p on l.id = p.linkid 
where p.userid = 155
order by case name when 'Home' then 2 when 'Team' then 1 end desc, name

Here is the error message I am receiving:

Msg 104, Level 16, State 1, Line 1
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

The bottom set above returns a list we use for link names, and the right column provides the path they link to. We need to add a default link for everyone which is why we are trying the union part since this link everyone will get and the other table displays links based on permission.

It works just fine without the order by clause, but i need the return to ipacs one at the top, then home, then team and rest ordered desc.

What am I doing wrong here?

Have you tried dropping the CASE? Also, TOP typically pairs with ORDER BY — Jason McCreary
– Jason McCreary, Commented Jul 2, 2013 at 21:49
@JasonMcCreary yeah it seems to work with out the case. But I need them sorted specifically for three of them. Might have to adopt the solution by Smandoli — James Wilson
– James Wilson, Commented Jul 2, 2013 at 21:53

andre.barata · Accepted Answer · 2013-07-02 22:07:22Z

2

How about:

select 
    'Return To IPACS' as name, 
    'http://domain:88' as link, 
    1 as sort_me 
union all
(select 
    name, 
    link, 
    2 as sort_me 
from jrm_intranetlinks l 
inner join jrm_intranetpermissions p on l.id = p.linkid 
where p.userid = 155 )
order by sort_me

I am worried about your nesting. I added some parenthesis. Anyway, the point is that you should just add values as shown to force the sort.

edited Jul 2, 2013 at 22:07

andre.barata

6633 silver badges11 bronze badges

answered Jul 2, 2013 at 21:49

Smandoli

7,0593 gold badges53 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

James Wilson Over a year ago

I was able to adopt this and it works like a charm now. Thank you.

T I · Accepted Answer · 2013-07-02 22:06:18Z

2

select 
    'Return To IPACS' as name, 
    'www.home.com' as link,
    3 sortOrder
union all
select 
    name, 
    link,
    case name 
        when 'Home' then 2 
        when 'Team' then 1 
    end sortOrder
from links l
inner join jrm_intranetpermissions p on l.id = p.linkid 
where p.userid = 155 
order by sortOrder desc

demo

answered Jul 2, 2013 at 22:06

T I

9,9634 gold badges31 silver badges51 bronze badges

Comments

BenMaGoo · Accepted Answer · 2013-07-02 22:14:06Z

1

What you have are two different "columns"...

l.[Name]

and

(case [Name] when 'Home' then 2 when 'Team' then 1 end)

...the former is in the select list and the latter is not.

Several workarounds exist. My favorite is...

select top 1 'Return To IPACS' as [Name]
    ,'http://domain:88' as [Link]
    , 3 as [Order]

union 

select l.name as [Name]
    ,l.link as [Link]
    ,(case [Name] when 'Home' then 2 when 'Team' then 1 end) as [Order]
from jrm_intranetlinks l
inner join jrm_intranetpermissions p on l.id = p.linkid 
where p.userid = 155
order by [Order], [Name]

answered Jul 2, 2013 at 22:14

BenMaGoo

451 silver badge8 bronze badges

Comments

Alex Gordon · Accepted Answer · 2013-07-02 21:50:26Z

0

You are only selecting "name" and "link" so those are the only items you can use in your ORDER BY when you are using a UNION, INTERSECT, or EXPECT.

In otherwords, you need to add all the items in your 'order by' clause to your select statement.

edited Jul 2, 2013 at 21:50

Alex Gordon

61.5k307 gold badges710 silver badges1.1k bronze badges

answered Jul 2, 2013 at 21:49

Nick

3,1763 gold badges23 silver badges25 bronze badges

4 Comments

James Wilson Over a year ago

I only have name and link in both selects? Do you know how the case messes this up?

andre.barata Over a year ago

the case in the order clause shouldn't be a problem

James Wilson Over a year ago

@andre.barata I can't think of what else it would be. It works without the case, and errors with it.

Nick Over a year ago

Your case statement seems off blog.sqlauthority.com/2007/07/17/…

andre.barata · Accepted Answer · 2013-07-02 21:53:20Z

0

Did you try making it an inner query and specifiyng the order outside?

Something like this:

select name, link
from (
    select top 1 'Return To IPACS' as name, 'http://domain:88' as link 
    union 
    select name,link 
    from jrm_intranetlinks l 
    inner join jrm_intranetpermissions p on l.id = p.linkid 
    where p.userid = 155
) tbl
order by case name when 'Home' then 2 when 'Team' then 1 end desc, name

answered Jul 2, 2013 at 21:53

andre.barata

6633 silver badges11 bronze badges

1 Comment

andre.barata Over a year ago

also consider changing the union to union all, since the union keyword also makes the results distinct

Anon · Accepted Answer · 2013-07-02 22:03:37Z

0

select name,link from (
  select
    'Return To IPACS' as name,
    'http://domain:88' as link,
    0 as sort_order
  union 
  select
    name,
    link
    case name when 'Home' then 1 when 'Team' then 2 as sort_order
  from jrm_intranetlinks l 
  inner join jrm_intranetpermissions p on l.id = p.linkid 
  where p.userid = 155
) t
order by sort_order, name

answered Jul 2, 2013 at 22:03

Anon

10.9k1 gold badge32 silver badges31 bronze badges

Collectives™ on Stack Overflow

SQL Union problems when using Order By

6 Answers 6

1 Comment

Comments

Comments

4 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

Comments

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related