How do I return a few of my resultset rows at the bottom of the entire resultset

Let us see the scenario one more time. 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
GOSELECT [ProductID], COUNT(*) CountofProductIDFROM [Sales].[SalesOrderDetail]WHERE ProductID BETWEEN 707 AND 716GROUP BY [ProductID]
GO

Now instead of writing CASE statement in ORDER BY clause we will now write UNION ALL clause. In this case if you see there are two different values which we want at the bottom of the resultset.

USE AdventureWorks2012
GOSELECT [ProductID], COUNT(*) CountofProductIDFROM [Sales].[SalesOrderDetail]WHERE (ProductID BETWEEN 707 AND 716)
AND ProductID <> 715 AND ProductID <> 712GROUP BY [ProductID]UNION ALLSELECT [ProductID], COUNT(*) CountofProductIDFROM [Sales].[SalesOrderDetail]WHERE ProductID = 715GROUP BY [ProductID]UNION ALLSELECT [ProductID], COUNT(*) CountofProductIDFROM [Sales].[SalesOrderDetail]WHERE ProductID = 712GROUP BY [ProductID]
GO

Here is the resultset which we expected. We have to use two different ORDER BY clause to get the desired result. In our case if we have more than 2 special cases like these we will need keep on adding more and more UINON clauses and that will make this script more confusing and not easy to read at all.

Now let us compare the performance of the two different queries one from earlier blog post and one from current blog post. Execute following two queries together and check their execution plan. In the execution plan can be enabled by using CTRL + M keyword.

-- Method 1 - CASE and ORDER BYUSE AdventureWorks2012
GOSELECT [ProductID], COUNT(*) CountofProductIDFROM [Sales].[SalesOrderDetail]WHERE ProductID BETWEEN 707 AND 716GROUP BY [ProductID]ORDER BY CASE WHEN [ProductID] = 715 THEN 1WHEN [ProductID] = 712 THEN 2 ELSE 0 ENDGO-- Method 2 - UNION ALLUSE AdventureWorks2012
GOSELECT [ProductID], COUNT(*) CountofProductIDFROM [Sales].[SalesOrderDetail]WHERE (ProductID BETWEEN 707 AND 716)
AND ProductID <> 715 AND ProductID <> 712GROUP BY [ProductID]UNION ALLSELECT [ProductID], COUNT(*) CountofProductIDFROM [Sales].[SalesOrderDetail]WHERE ProductID = 715GROUP BY [ProductID]UNION ALLSELECT [ProductID], COUNT(*) CountofProductIDFROM [Sales].[SalesOrderDetail]WHERE ProductID = 712GROUP BY [ProductID]
GO

You will clearly notice that the solution with CASE and ORDER BY is a much better scenario than using UNION ALL clause.

So far we have seen two examples 1) CASE and ORDER BY clause and 2) UNION ALL clause. If you know any other trick to get the similar result, please leave a comment and I will post this on my blog with due credit.