SQL SERVER – Return Specific Row to at the Bottom of the Resultset – T-SQL Script

“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.

SQL SERVER - Return Specific Row to at the Bottom of the Resultset - T-SQL Script orderbycase

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


SQL SERVER - Return Specific Row to at the Bottom of the Resultset - T-SQL Script orderbycase1

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.

SQL SERVER - Return Specific Row to at the Bottom of the Resultset - T-SQL Script orderbycase2

Let me know what other solution which you would like to propose.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Scripts, SQL Server
Previous Post
SQL SERVER – Discussion – Scale-up vs Scale-out Architectures
Next Post
SQL SERVER – Interview Questions and Answers Sample Chapter Free Download – SQL in Sixty Seconds #050

Related Posts

23 Comments. Leave new

  • RAKESH HOLKAR
    April 23, 2013 9:47 am

    Wow… Thanks Pinal, looking useful for me

    Reply
  • Really very good and useful.

    Reply
  • SELECT [ProductID], COUNT(*) CountofProductID
    FROM [Sales].[SalesOrderDetail]
    WHERE ProductID BETWEEN 707 AND 716 AND [ProductID] NOT IN (715,712)
    UNION ALL
    SELECT [ProductID], COUNT(*) CountofProductID
    FROM [Sales].[SalesOrderDetail]
    WHERE ProductID IN (715 AND 712)

    Sir i m having same results by above query..Please tell me what’s the difference ?

    Reply
  • Group By [ProductID] should be there.. :-(

    Reply
  • CREATE TABLE RecordBelow(ID INT IDENTITY NOT NULL,Name varchar(15) not null default ”)
    GO
    INSERT INTO RecordBelow (NAME)VALUES(‘Manish’)
    GO 10
    GO
    SELECT ID , NAME FROM RecordBelow WHERE ID BETWEEN 1 AND 10 AND ID NOT IN (5,6)
    UNION ALL
    SELECT ID , NAME FROM RecordBelow WHERE ID IN (5,6)
    GO

    This is what i tried sir …..
    I hv seen a lot of people in my small company trying this approach..
    And i think its not optimized query as the one u have suggested in this blog post..

    Reply
  • Thanks

    Reply
  • Got the point ..Thanx Sir

    Reply
  • If we want to display on top then we have to reverse the condition and above query can also as–>

    USE AdventureWorks2012
    GO
    SELECT [ProductID], COUNT(*) CountofProductIDFROM [Sales].[SalesOrderDetail]
    WHERE ProductID BETWEEN 707 AND 716
    GROUP BY [ProductID]
    ORDER BY (CASE Productid when 712 then 0
    when 715 then 1 else 0 end), productid
    Go

    Reply
  • Great Sir…
    You complete our day by your Primary Key type (Unique) tips….

    Reply
  • Hello Pinal

    if we want ant record at the Top in result set then what should we do?

    please explain this in details.

    Reply
    • I got the answer

      Just need to add Order by …. desc

      SELECT ProductId, COUNT(*) CountofProductID
      FROM Product
      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 desc

      Reply
  • Result not get proper in my case. I have write following query

    SELECT VoucherTypeCode, Count(*) As TotalCount
    FROM tranInwardHeader
    GROUP BY tranInwardHeader.VoucherTypeCode
    Order by tranInwardHeader.VoucherTypeCode

    Which gives me result like below:

    VoucherTypeCode TotalCount
    202 597
    203 378
    204 44
    205 534
    302 637
    402 255

    After that i decided 203 Voucher Type Always be last row and 302 always be Second Last and modify the query like below:

    SELECT VoucherTypeCode, Count(*)
    FROM tranInwardHeader
    GROUP BY tranInwardHeader.VoucherTypeCode
    Order by CASE WHEN tranInwardHeader.VoucherTypeCode = 203 THEN 2
    WHEN tranInwardHeader.VoucherTypeCode = 302 THEN 1
    ELSE 0
    END

    Which gives me following result:

    VoucherTypeCode TotalCount
    402 255
    204 44
    202 597
    205 534
    302 637
    203 378

    Last two rows came proper as per which i decided but above other rows not coming in proper order by Voucher Type Code. Why 402 voucher type came as first row?

    Please give your inputs.

    Reply
    • Chalapathi Pinisetty
      December 28, 2013 12:20 pm

      Hi, I guess it’s because first query is order by ‘tranInwardHeader.VoucherTypeCode’ where you miss that in second query. Try below query and see.

      SELECT VoucherTypeCode, Count(*)
      FROM tranInwardHeader
      GROUP BY tranInwardHeader.VoucherTypeCode
      Order by CASE WHEN tranInwardHeader.VoucherTypeCode = 203 THEN 2
      WHEN tranInwardHeader.VoucherTypeCode = 302 THEN 1
      ELSE 0
      END,tranInwardHeader.VoucherTypeCode

      Reply
  • USE AdventureWorks2012
    SELECT ProductID, COUNT(*) CountofProductID
    FROM Sales.SalesOrderDetail
    WHERE ProductID BETWEEN 707 AND 716 AND ProductID NOT IN (715,712)
    GROUP BY ProductID
    UNION ALL
    SELECT ProductID, COUNT(*) CountofProductID
    FROM Sales.SalesOrderDetail
    WHERE ProductID IN (715,712)
    GROUP BY ProductID

    Reply
  • Hi Pinal,

    What if you wanted to display specific row(s) not at the top or bottom but lets say at the 5th position or the 10th position, with the remaining rows sorted in ascending or descending order?

    Based on your example,

    [ProductID] Count

    707 XXXXX
    708 XXXXX
    709 XXXXX
    712 XXXXX
    710 XXXXX
    711 XXXXX
    713 XXXXX

    In the above, I have productid sorted in ascending order and the id 712 is displayed in the 4th position instead of the expected location of 6th position.

    Thanks in advance for any help..

    Reply
  • This solution was fantastic – thanks!

    On another note, I used to use the UNION ALL method to add a row to the end of my result set, but that did not work for my current problem. This worked great though.

    With my current problem, let’s say the ProductId and Counts were already in a table. When selecting from this table, I want to ensure the result set has the ProductId’s in order, except 712 should be at the end. When using the UNION ALL method, there was no way to force the order for just the non-712 rows (since you cannot order until after the data is unioned).

    With the case statement in the order by clause, I could just continue to add ordering criteria. In my example,
    ORDER BY CASE WHEN [ProductID] = 712 THEN 1 ELSE 0 END, ProductID

    This did exactly what I needed! :)

    Reply
  • It is not working for String Values

    Reply

Leave a Reply