Solved

Problem with union query containing inner join and top 10 clauses

Posted on 2004-08-23
13
438 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
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
 
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

911 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

21 Experts available now in Live!

Get 1:1 Help Now