Find First And Last Record using SQL Server

Let us see how to find the first and last record of a table based on a criteria. For demonstration purpose we will be using the 'Orders' table of the Northwind database. Our criteria in this example will be to find the First and Last Record of the Customer 'ALFKI' in this table.

Here's the query to do so:

WITH CTE
AS
(SELECT CustomerID,OrderDate,Freight,ShipName,
ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY OrderDate) as StartRec,
ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY OrderDate DESC) as EndRec
FROM ORDERS WHERE CustomerID='ALFKI')
SELECT CustomerID,OrderDate,Freight,ShipName
FROM CTE WHERE StartRec=1 or EndRec=1
ORDER BY OrderDate

Note: If you wish to find out the first and last record for all the customers in this table, just remove the condition WHERE CustomerID = 'ALFKI'.