Have you ever encountered a situation where you needed to return specific rows at the bottom of your resultset in SQL Server? In this blog post, we will explore different methods to achieve this requirement.

One common approach is to use the CASE statement in the ORDER BY clause. This allows you to specify custom sorting logic based on certain conditions. For example, let’s say we have a resultset ordered by the ProductID column, and we want row 715 to be the second last row and row 712 to be the last row. We can achieve this by writing a query like this:

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

This approach works well for a small number of special cases. However, if you have more than a few special cases, the query can become complex and difficult to read.

Another method to manipulate the resultset order is by using the UNION ALL clause. This approach involves writing multiple SELECT statements and combining the results using UNION ALL. Each SELECT statement can have its own WHERE clause to filter out the desired rows. For example, to achieve the same result as the previous example, we can write the following query:

SELECT [ProductID], COUNT(*) CountofProductID
FROM [Sales].[SalesOrderDetail]
WHERE (ProductID BETWEEN 707 AND 716) AND ProductID <> 715 AND ProductID <> 712
GROUP BY [ProductID]
UNION ALL
SELECT [ProductID], COUNT(*) CountofProductID
FROM [Sales].[SalesOrderDetail]
WHERE ProductID = 715
GROUP BY [ProductID]
UNION ALL
SELECT [ProductID], COUNT(*) CountofProductID
FROM [Sales].[SalesOrderDetail]
WHERE ProductID = 712
GROUP BY [ProductID]

Comparing the performance of these two methods, it is clear that the solution with the CASE statement and ORDER BY clause is more efficient. The UNION ALL approach requires multiple SELECT statements and can result in a more complex execution plan.

If you know any other tricks or methods to manipulate the resultset order in SQL Server, please leave a comment below. I would love to hear your insights and share them with the community.