A CTE can help retrieve the first and last rows together. The important detail is the ordering. Each endpoint needs its own ordered query before the two results are combined.
This continues the original TOP and BOTTOM rows question. The question came from an inventory project where I needed both endpoints. Thanks to Khadar Khan for contributing a CTE approach to the discussion.
Keep each ORDER BY inside its own CTE
The earlier example placed two ORDER BY clauses around UNION ALL inside one CTE. That form is invalid. The corrected example below uses two CTEs, one for each endpoint.
DECLARE @Items TABLE (ItemId int PRIMARY KEY, ItemName varchar(30));
INSERT INTO @Items (ItemId, ItemName)
VALUES (30, 'Notebook'), (10, 'Pencil'), (20, 'Folder');
;WITH FirstItem AS
(
SELECT TOP (1) ItemId, ItemName
FROM @Items
ORDER BY ItemId ASC
),
LastItem AS
(
SELECT TOP (1) ItemId, ItemName
FROM @Items
ORDER BY ItemId DESC
)
SELECT 'First' AS Boundary, ItemId, ItemName FROM FirstItem
UNION ALL
SELECT 'Last', ItemId, ItemName FROM LastItem
ORDER BY ItemId, Boundary;The result contains item 10, Pencil, as First and item 30, Notebook, as Last. The insertion order does not decide either endpoint. The ItemId ordering does.
Decide what first and last mean
A table has no inherent first or last row. Choose the column that expresses your requirement. If you sort by a nonunique date, add a unique key to break ties consistently.
The ORDER BY inside each CTE controls which row TOP selects. The final ORDER BY controls how the combined result appears. Those are separate decisions.
Watch the small-table cases
An empty table returns no rows. A table with one row returns it twice, once for each boundary. That is intentional here because the output labels both endpoints.
If you need unique rows instead, remove the Boundary labels and combine the endpoint columns with UNION. A CTE names a query result for one statement. It doesn’t create a stored temporary table or guarantee a faster plan.
Have you used another approach for this requirement? The comments on the original question include several useful alternatives.
The first row is not the first one inserted, it is the first one in your chosen order.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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