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.

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.

Click to Download Scripts

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

About these ads

22 thoughts on “SQL SERVER – Return Specific Row to at the Bottom of the Resultset – T-SQL Script

  1. 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 ?

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

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

  4. Pingback: SQL SERVER – Return Specific Row to at the Bottom of the Resultset – T-SQL Script – Part 2 | SQL Server Journey with SQL Authority

  5. Pingback: SQL SERVER – UNION ALL and UNION are Different Operation | SQL Server Journey with SQL Authority

    • 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

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

    • 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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s