Solved

Problem with union query containing inner join and top 10 clauses

Posted on 2004-08-23
13
436 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
  • 4
  • 4
  • 2
  • +2
13 Comments
 
LVL 17

Expert Comment

by:BillAn1
Comment Utility
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
Comment Utility
Try it without the 'ORDER BY' clauses.
0
 
LVL 4

Expert Comment

by:davehilditch
Comment Utility
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
 
LVL 17

Expert Comment

by:BillAn1
Comment Utility
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
Comment Utility
Oh yeah, sorry I missed his TOP 10 operator.  

Dave Hilditch.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
Comment Utility
Never mind, ignore that last comment.  Not thinking straight.
0
 

Author Comment

by:tomfolinsbee
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now