472,353 Members | 1,921 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,353 software developers and data experts.

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 10975
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Paradigm | last post by:
I am using Access 2K as a front end to a MYSQL database. I am trying to run a Union query on the MYSQL database. The query is (much simplified)...
14
by: Salad | last post by:
A97. Situation: I have 3 tables with a text field in each and a date field in the first 2 tables: Table1 Text1, Date1 Table2 Text2, Date2...
5
by: Lyn | last post by:
This one is difficult to explain, so I will cut it down to the basics. I have a major table 'tblA' which has an autonum field 'ID-A' as primary...
2
by: mattytee123 | last post by:
I have about 20 tables, of which I would like to do a union query and count of how many of each different code there is? The simplified verson of...
2
by: marco | last post by:
Dear List, as it seems, MS SQL as used in Access does not allow a select INTO within a UNION query. Also, it seems that a UNION query can not be...
3
by: mikes | last post by:
I have 2 separate queries, which effectively are the same except they draw data from separate tables. Both tables are (design-wise) identical, only...
5
by: BillCo | last post by:
I'm having a problem with a union query, two simple queries joined with a union statement. It's created in code based on parameters. Users were...
7
by: KoliPoki | last post by:
Hello every body. I have a small issue. Problem: I have a table with 4 descriptor columns (type). I need to formulate a query to retrieve a...
5
by: wugon.net | last post by:
question: db2 LUW V8 UNION ALL with table function month() have bad query performance Env: db2 LUW V8 + FP14 Problem : We have history data...
27
by: MLH | last post by:
How can I turn the following into a make-table query? SELECT & " " & AS Recipient FROM tblVehicleJobs INNER JOIN tblAddnlOwnrs ON...
1
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
0
jalbright99669
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
0
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web...
0
Oralloy
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the...
0
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand....
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS...
0
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.