Solved

Problem with union query containing inner join and top 10 clauses

Posted on 2004-08-23
13
449 Views
Last Modified: 2012-05-05
I'm trying to write a query to fetch 10 records of each DocTypeID. The first Select statement works fine. When I add the union clause and a second select statement with a different DocTypeID, I get an error about syntax error near Union clause.

I understand that a union query is should only have ORDER BY in the last clause, but then the first clause wouldn't know which field to sort the top 10 records.

Here's the query:

SELECT     TOP 10 Docs.ID, Docs.Code, DocTypes.Name AS DocType, Docs.Title, Docs.Posted, Docs.DownloadTime
FROM         Docs INNER JOIN
                      DocTypes ON Docs.DocTypeID = DocTypes.ID
WHERE     (Docs.Posted = 0) AND (Docs.AssignedID = 0) AND (Docs.DocTypeID = 3) ORDER BY Docs.ID DESC  

UNION

SELECT     TOP 10 Docs.ID, Docs.Code, DocTypes.Name AS DocType, Docs.Title, Docs.Posted, Docs.DownloadTime
FROM         Docs INNER JOIN
                      DocTypes ON Docs.DocTypeID = DocTypes.ID
WHERE     (Docs.Posted = 0) AND (Docs.AssignedID = 0) AND (Docs.DocTypeID = 4) ORDER BY Docs.ID DESC

Thanks!

Tom  
0
Comment
Question by:tomfolinsbee
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +2
13 Comments
 
LVL 17

Expert Comment

by:BillAn1
ID: 11874248
You will need to do something like this  - encapsulate the two sub-queries, then select * from both, and order the global result set. You can use a dummy field called sec to keep the two halves seperate if that is what you need :

select Docs.ID, Docs.Code, DocTypes.Name AS DocType, Docs.Title, Docs.Posted, Docs.DownloadTime
FROM
(
SELECT     TOP 10 Docs.ID, Docs.Code, DocTypes.Name AS DocType, Docs.Title, Docs.Posted, Docs.DownloadTime, 1 as sec
FROM         Docs INNER JOIN
                      DocTypes ON Docs.DocTypeID = DocTypes.ID
WHERE     (Docs.Posted = 0) AND (Docs.AssignedID = 0) AND (Docs.DocTypeID = 3) ORDER BY Docs.ID DESC  
) a
UNION
select Docs.ID, Docs.Code, DocTypes.Name AS DocType, Docs.Title, Docs.Posted, Docs.DownloadTime
FROM (
SELECT     TOP 10 Docs.ID, Docs.Code, DocTypes.Name AS DocType, Docs.Title, Docs.Posted, Docs.DownloadTime, 2 as sec
FROM         Docs INNER JOIN
                      DocTypes ON Docs.DocTypeID = DocTypes.ID
WHERE     (Docs.Posted = 0) AND (Docs.AssignedID = 0) AND (Docs.DocTypeID = 4) ORDER BY Docs.ID DESC
) b
order by sec, ID desc
0
 
LVL 8

Expert Comment

by:bukko
ID: 11875431
Try it without the 'ORDER BY' clauses.
0
 
LVL 4

Expert Comment

by:davehilditch
ID: 11875920
Take out ORDER BY in the *first* part of the UNION and it will work.  You can keep your second ORDER BY clause which actually will order the entire result set.

Dave Hilditch.
0
Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

 
LVL 17

Expert Comment

by:BillAn1
ID: 11876052
No, if you just take away the order by from the two halves (or the first), you wont get the correct top 10 in each half You will simply get the first 10 records that SQL Server happens to find. You need to keep the "order by" , but isolate it inside a nested query.
0
 
LVL 4

Expert Comment

by:davehilditch
ID: 11876118
Oh yeah, sorry I missed his TOP 10 operator.  

Dave Hilditch.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11877519
I agree with BillAn1, but in addition you should change: UNION to UNION ALL or you could potentially end up with less than 20 (if there are duplicates).  It is also more efficient.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11877549
Somebody correct me if if I am wrong, but this should also work:

SELECT     TOP 10 Docs.ID, Docs.Code, DocTypes.Name AS DocType, Docs.Title, Docs.Posted, Docs.DownloadTime
FROM         Docs INNER JOIN
                      DocTypes ON Docs.DocTypeID = DocTypes.ID
WHERE     (Docs.Posted = 0) AND (Docs.AssignedID = 0) AND (Docs.DocTypeID = 3)
UNION ALL
SELECT     TOP 10 Docs.ID, Docs.Code, DocTypes.Name AS DocType, Docs.Title, Docs.Posted, Docs.DownloadTime
FROM         Docs INNER JOIN
                      DocTypes ON Docs.DocTypeID = DocTypes.ID
WHERE     (Docs.Posted = 0) AND (Docs.AssignedID = 0) AND (Docs.DocTypeID = 4) ORDER BY Docs.ID DESC

ORDER BY Docs.DocTypeID, Docs.ID DESC  
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11877558
Never mind, ignore that last comment.  Not thinking straight.
0
 

Author Comment

by:tomfolinsbee
ID: 11877568
Hello,

I tried BillAn's suggestion and I get this error message:

Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'Docs' does not match with a table name or alias name used in the query.

This part of the query works fine:
SELECT     TOP 10 Docs.ID, Docs.Code, DocTypes.Name AS DocType, Docs.Title, Docs.Posted, Docs.DownloadTime
FROM         Docs INNER JOIN
                      DocTypes ON Docs.DocTypeID = DocTypes.ID
WHERE     (Docs.Posted = 0) AND (Docs.AssignedID = 0) AND (Docs.DocTypeID = 3)
ORDER BY Docs.ID DESC

Any idea why I'm getting this error?

Tom
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 250 total points
ID: 11877699
I suspect somehting like this was intended (I also replace UNION with UNION ALL):

select ID, Code, DocType, Title, Posted, DownloadTime
FROM
(
SELECT     TOP 10 Docs.ID, Docs.Code, DocTypes.Name AS DocType, Docs.Title, Docs.Posted, Docs.DownloadTime, 1 as sec
FROM         Docs INNER JOIN
                      DocTypes ON Docs.DocTypeID = DocTypes.ID
WHERE     (Docs.Posted = 0) AND (Docs.AssignedID = 0) AND (Docs.DocTypeID = 3) ORDER BY Docs.ID DESC  
) a
UNION ALL
select Docs.ID, Docs.Code, DocTypes.Name AS DocType, Docs.Title, Docs.Posted, Docs.DownloadTime
FROM (
SELECT     TOP 10 Docs.ID, Docs.Code, DocTypes.Name AS DocType, Docs.Title, Docs.Posted, Docs.DownloadTime, 2 as sec
FROM         Docs INNER JOIN
                      DocTypes ON Docs.DocTypeID = DocTypes.ID
WHERE     (Docs.Posted = 0) AND (Docs.AssignedID = 0) AND (Docs.DocTypeID = 4) ORDER BY Docs.ID DESC
) b
order by sec, ID desc
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 11878895
sorry, yes that's exactly what I wanted to say!
in the outer select, the aliass / table names should be dropped
0
 

Author Comment

by:tomfolinsbee
ID: 11884160
Thanks!

I tried it and now get this message:

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.

So I put "sec" in the first row and now get these errors:


Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'Docs' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'Docs' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'DocTypes' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'Docs' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'Docs' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'Docs' does not match with a table name or alias name used in the query.

Really appreciate everyone's help with this.
0
 
LVL 17

Accepted Solution

by:
BillAn1 earned 250 total points
ID: 11884298
for both halves of the query you need to drop the aliases :

select ID, Code, DocType, Title, Posted, DownloadTime,sec
FROM
(
SELECT     TOP 10 Docs.ID, Docs.Code, DocTypes.Name AS DocType, Docs.Title, Docs.Posted, Docs.DownloadTime, 1 as sec
FROM         Docs INNER JOIN
                      DocTypes ON Docs.DocTypeID = DocTypes.ID
WHERE     (Docs.Posted = 0) AND (Docs.AssignedID = 0) AND (Docs.DocTypeID = 3) ORDER BY Docs.ID DESC  
) a
UNION ALL
select ID, Code, DocType, Title, Posted, DownloadTime, sec
FROM (
SELECT     TOP 10 Docs.ID, Docs.Code, DocTypes.Name AS DocType, Docs.Title, Docs.Posted, Docs.DownloadTime, 2 as sec
FROM         Docs INNER JOIN
                      DocTypes ON Docs.DocTypeID = DocTypes.ID
WHERE     (Docs.Posted = 0) AND (Docs.AssignedID = 0) AND (Docs.DocTypeID = 4) ORDER BY Docs.ID DESC
) b
order by sec, ID desc
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question