469,609 Members | 1,681 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,609 developers. It's quick & easy.

union with sub query

I've been trying to do a union with a subquery - I've made a different
example which follows the same principles as follows:

First bit brings back accounts which are in the top 10 to 15 by account
name.

Second bit brings back accounts which are in the bottom 10 to 15 by
account name.

I want to union the two result sets together. These selects work as
they are, but don't when i take the comment away from the union
operator.

select top 5 c1.accountno
from tbl_customer c1
where c1.accountno not in
(
select top 10 c2.accountno
from tbl_customer c2
order by c2.accountName asc
)
order by c1.accountName asc

--union all

select top 5 c1.accountno
from tbl_customer c1
where c1.accountno not in
(
select top 10 c2.accountno
from tbl_customer c2
order by c2.accountName desc
)
order by c1.accountName desc

So my problem is really about how to have an order by in a sub query
which is then used in a main query which is then unioned with another
query - SQL Server doesn't seem to like that combination of things. Any
clues anyone?

Cheers,
NAJH

Jul 23 '05 #1
5 10803
You can only have one ORDER BY at the end of the UNION - UNION produces
a set, so sorting individual parts of the set wouldn't make much sense.
In your case, since you need ORDER BY in each query to use TOP, you can
turn each query into a derived table, then UNION the derived tables.

Simon

select 'A' as 'Sort', accountno
from
(
select top 5 c1.accountno
from tbl_customer c1
where c1.accountno not in
(
select top 10 c2.accountno
from tbl_customer c2
order by c2.accountName asc
)
order by c1.accountName asc
) dt1
union all
select 'B', accountno
from
(
select top 5 c1.accountno
from tbl_customer c1
where c1.accountno not in
(
select top 10 c2.accountno
from tbl_customer c2
order by c2.accountName desc
)
order by c1.accountName desc
) dt2
order by Sort, accountno

Jul 23 '05 #2
That makes sense to me, thank you.

Now we come to the crux of my problem:

Server: Msg 104, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if the statement contains
a UNION operator.

The key problem seems to be that I can't have an "order by" in the sub
query. Any way around it?

Jul 23 '05 #3
My mistake for not testing properly... As the error says, you need to
include the ORDER BY item - in this case accountName - in the derived
table query:

select 'A' as 'Sort', accountno
from
(
select top 5 c1.accountno, c1.accountName
from tbl_customer c1
where c1.accountno not in
(
select top 10 c2.accountno
from tbl_customer c2
order by c2.accountName asc
)
order by c1.accountName asc
) dt1
union all
....

A simpler example might be clearer - you must include the 'name' column
in the derived tables here, although it isn't used in the output:

select 'A' as 'Sort', id
from (
select top 10 id, name
from sysobjects
order by name desc
) dt1
union all
select 'B', id
from (
select top 10 id, name
from sysobjects
order by name asc
) dt2
order by Sort, id

Simon

Jul 23 '05 #4
Thanks for your response. I need to use it in a "where not in" type sub
query though, so that wouldn't work as you can only return one column.
Looking into the problem further it seems that it's some sort of
conflict between ansi SQL 99 standard and the ansi sql 92 standard.

The ANSI SQL-99 standard allows you to ORDER BY a column that is not
part of the selection list while the ANSI SQL-92 standard does not
allow this. According to this standard, the ORDER BY column needs to be
part of the selection list.

I found this in the posting:

Subject: Problems with UNION and ORDER BY
Newsgroups: comp.databases.ms-sqlserver
Date: 2002-12-30 04:55:10 PST

The solution was to put the whole union inside the from part of the
query and then do a select * from the unioned query.

eg:
select doodah.*
from
(
select blahblah1
from something1
where not in (select blahblah2 from something2)
union
select blahblah3
from something3
where not in (select blahblah4 from something4)
)as doodah

So there we go. Hopefully those doodahs blahs and somethings make sense
to anyone puzzled with this in future!
Cheers.

Jul 23 '05 #5
>> So my problem is really about how to have an order by in a sub query
which is then used in a main query which is then unioned with another
query - SQL Server doesn't seem to like that combination of things. Any
clues anyone? <<

You missed the most basic concept of an RDBMS. Tables are not ordered;
cursors have an ORDER BY that is applied to the whole result set after
it is constructed.

Also, we do not put that silly 'tbl_" prefix on names and uses
collective or plural names (do you really have one piece of furniture
as your customer?) You might want to read ISO-11179 or a good book on
data modeling.

A good SQL programmer also avoids proprietary extensions like TOP.
Especially if those extensions are non-relational.

Finally, UNIONs between SELECTs on the same table can usually be
avoided. What are you trying to do?

Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Paradigm | last post: by
2 posts views Thread by mattytee123 | last post: by
7 posts views Thread by KoliPoki | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.