473,325 Members | 2,308 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 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 11046
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) SELECT as ID from faxdata UNION SELECT as ID ...
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 Table3 Text3 (no date field) The following...
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 key (of no significance to users). 'tblA' contains...
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 the table is structured like this. Code ...
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 used as a subquery. Maybe my (simplified)...
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 the data is different. for each query, there are...
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 noticing some inconsistant data and when I analysed...
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 count for each type so I can group by...etc. The...
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 from 2005/01/01 ~ 2007/05/xx in single big...
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 tblVehicleJobs.VehicleJobID = tblAddnlOwnrs.VehicleJobID WHERE...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.