Select Top (some number) with UNION ALL and ORDER BY

Question

I am trying to pull back the top five instances of certain records loaded into a table. It is important to note that I am trying to get my results out of the same table and I think there is a problem that I can't quite figure out related to the fact that this is one table. Here is the example of my query:

Select * From (
Select Top 5 JobID From Jobs Where JobTypeID = 1 Order By JobID DESC
UNION ALL
Select Top 5 JobID From Jobs Where JobTypeID = 2 Order By JobID DESC
UNION ALL
Select Top 5 JobID From Jobs Where JobTypeID = 3 Order By JobID DESC
UNION ALL
Select Top 5 JobID From Jobs Where JobTypeID = 4 Order By JobID DESC
UNION ALL
Select Top 5 JobID From Jobs Where JobTypeID = 5 Order By JobID DESC
UNION ALL
Select Top 5 JobID From Jobs Where JobTypeID = 6 Order By JobID DESC
) As UnionTable

When I run this is SQL Server I only get 9 records when I know in fact that there should be 30. How can I make sure that I pull back all of the correct records? Do I need to use a Group By clause in each subquery?

Answer 1

Run each SELECT statement separately and see if you get 5 rows for each

Run this to see the count for each, second column returned will be the JobID

Select COUNT(*),1 From Jobs Where JobID = 1 
UNION ALL
Select COUNT(*),2 From Jobs Where JobID = 2 
UNION ALL
Select COUNT(*),3 From Jobs Where JobID = 3 
UNION ALL
Select COUNT(*),4 From Jobs Where JobID = 4 
UNION ALL
Select COUNT(*),5 From Jobs Where JobID = 5 
UNION ALL
Select COUNT(*),6 From Jobs Where JobID = 6 

you can also run it like this

Select COUNT(*),JobID
 From Jobs 
 Where JobID between 1 and 6 
 GROUP By JobID

Answer 2

More elegant..

;WITH cte AS
(
    SELECT
         JobID, 
         ROW_NUMBER() OVER (PARTITION BY JobTypeID ORDER BY JobID /* eh? */ DESC) AS rank
    FROM
         Jobs WHERE JobTypeID BETWEEN 1 AND 6
)
SELECT * FROM CTE WHERE rank <= 5

Why do you have both WHERE and ORDER by JobID? This makes the ORDER BY clause meaningless...