Select Top (some number) with UNION ALL and ORDER BY
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
UN开发者_如何学编程ION 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?
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...
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
精彩评论