Please read SQL SERVER – How to Retrieve TOP and BOTTOM Rows Together using T-SQL before continuing this article. I had asked users to come up with an alternate solution of the same problem. In this blog post we will see solution with the help of CTE.
Let us see following example provided by Khadar Khan. This example is using the newly introduced concept of CTE of SQL Server 2005. CTE acts as a temporary result set for the query.
WITH TopBottomRow AS ( SELECT TOP 1 SalesOrderDetailID FROM Sales.SalesOrderDetail ORDER BY SalesOrderDetailID UNION ALL SELECT TOP 1 SalesOrderDetailID FROM Sales.SalesOrderDetail ORDER BY SalesOrderDetailID DESC ) SELECT * FROM TopBottomRow
Just a day ago, while working with some inventory related projects, I faced one interesting situation. I had to find TOP 1 and BOTTOM 1 record together. I right away that I should just do UNION but then I realize that UNION will not work as it will only accept one ORDER BY clause. If you specify more than one ORDER BY clause. It will give an error. Let us see how we can retrieve top and bottom rows together.
Let me know if there are any other methods to do the same tasks. I am very much interested to know if you know alternate solution.
Reference: Pinal Dave (https://blog.sqlauthority.com), Khadar Khan
7 Comments. Leave new
Thanks for sharing this.
I was used to do the same as follows:
SELECT * FROM
(
(SELECT MIN(SalesOrderDetailID) AS ID
FROM Sales.SalesOrderDetail
)
UNION
(SELECT MAX(SalesOrderDetailID) AS ID
FROM Sales.SalesOrderDetail
)
) AS t_TopBottomRow;
Hi ,
I have one query. How to add two row values togather in one table.
Min Max solution would not work for retrieving more than top one or bottom row. The original solution however will be able to retrieve the top 10 as well as bottom 10.
Deep…. There can be multiple scenarios for this. If you want to add values from existing table then you will have to write a statement like….
INSERT INTO TABLE_NAME SELECT EXISTING_ROWS FROM ANOTHER_TABLE/TABLE_NAME
To insert static values, you could do something like this….
INSERT INTO TABLE_NAME
SELECT 1,2,3,4,5
UNION
SELECT 6,7,8,9,10
how to retrieve a particular column.
This is an very helpful solution however, can you show how we can do this with a Group By in the top and bottom queries. It appears I cannot use an OrderBy with a group by. .
WITH TopBottomRow
AS
(
SELECT TOP 1 SalesOrderDetailID, Sum (Sales)
FROM Sales.SalesOrderDetail
Group By SalesOrder
ORDER BY SalesOrderDetailID
UNION ALL
SELECT TOP 1 SalesOrderDetailID, Sum(Sales)
FROM Sales.SalesOrderDetail
Group By SalesOrder
ORDER BY SalesOrderDetailID DESC
)
SELECT *
FROM TopBottomRow