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'.

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook