“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 (https://blog.sqlauthority.com)
23 Comments. Leave new
Wow… Thanks Pinal, looking useful for me
Really very good and useful.
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 ?
Hi Manish,
Please execute your query – it does not work.
I tried to fix it and the order of the productid column is not as required.
Group By [ProductID] should be there.. :-(
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..
Hi Manish,
Please pay attention to the crucial issue described in the blog – it is about order of the column. That is the issue.
Thanks
Got the point ..Thanx Sir
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
Jaymin
Have ever tested your query?
yes tested and its working
Dear Manoj
It is working and I had tested in my 2 projects
Thanks, saved my day.
Great Sir…
You complete our day by your Primary Key type (Unique) tips….
Hello Pinal
if we want ant record at the Top in result set then what should we do?
please explain this in details.
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
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
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
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..
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! :)
It is not working for String Values