The following
Microsoft SQL Server 2008 T-SQL database script in SSMS Query Editor demonstrates the use of the TOP function to get both TOP and BOTTOM of a table in a single query.
-- T-SQL find top & bottom 5 rows in a table applying a single query
-- Find head and tail of table
USE AdventureWorks2008;
GO
SELECT OrderDate = convert(DATE,OrderDate,111),
sod.*
FROM Sales.SalesOrderDetail sod
INNER JOIN Sales.SalesOrderHeader soh
ON soh.SalesOrderID = sod.SalesOrderID
WHERE SalesOrderDetailID IN (SELECT TOP 5 SalesOrderDetailID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID)
OR -- Descending sort will give the BOTTOM even though TOP is used
SalesOrderDetailID IN (SELECT TOP 5 SalesOrderDetailID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID DESC)
ORDER BY SalesOrderID
GO
/*
OrderDate SalesOrderID SalesOrderDetailID
2001-07-01 43659 1
2001-07-01 43659 2
2001-07-01 43659 3
2001-07-01 43659 4
2001-07-01 43659 5
2004-07-31 75122 121313
2004-07-31 75122 121314
2004-07-31 75123 121315
2004-07-31 75123 121316
2004-07-31 75123 121317
*/
|