“How do I return a few of my resultset rows at the bottom of the entire resultset?”
This is a very simple question – let me explain this with following diagram. In this following diagram you can notice that there are two rows which are with ProductID 712 and 715. The entire resultset is ordered by column ProductID. Now our final requirement is that we want row 715 to be the second last row in the resultset and 712 as a very last row in the resultset. Looking from outside the entire thing looks very simple however, in reality it is not as simple as it looks.
First look at the image below and see if you can come up with the solution to this problem.
Here is the script on AdventureWorks database which I have written generates the result as we have displayed in the image below.
USE AdventureWorks2012 GO SELECT [ProductID], COUNT(*) CountofProductID FROM [Sales].[SalesOrderDetail] WHERE ProductID BETWEEN 707 AND 716 GROUP BY [ProductID] GO
Now what we want is that we want only two records which are 715 and 712 at the bottom of the resultset. There are two different ways we can achieve that, one with the method which I have demonstrated below where I write ORDER BY clause and include the CASE statement there and second method is where I use UNION clause. I prefer to use the method displayed below as it always works efficiently and consistantly.
USE AdventureWorks2012 GO SELECT [ProductID], COUNT(*) CountofProductID FROM [Sales].[SalesOrderDetail] WHERE ProductID BETWEEN 707 AND 716 GROUP BY [ProductID] ORDER BY CASE WHEN [ProductID] = 715 THEN 1 WHEN [ProductID] = 712 THEN 2 ELSE 0 END GO
Here is the resultset which we expected. In the ORDER BY clause we have use CASE statement to achieve following result. We can add more case statement if we have more than one rows to treat differently.
Let me know what other solution which you would like to propose.
Reference: Pinal Dave (http://blog.sqlauthority.com)