How to display only first and last record using TSQL

Here let us see how to get only first and last record of a table. 

Note: For demonstration purpose I am using 'Orders' table of the Northwind database. 

Query:

/*Query: 1 - To show all the records in orders table*/

SELECT * FROM ORDERS ORDER BY CustomerId ASC, OrderDate ASC

/*Query: 2 - To fetch first and last record in orders table*/

WITH CTETable

AS

(SELECT OrderId, 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)

SELECT OrderId, CustomerID,OrderDate,Freight,ShipName

FROM CTETable WHERE StartRec=1 or EndRec=1

ORDER BY CustomerId ASC, OrderDate ASC

Output:

Here query: 1 will display all the rows in the orders table and query: 2 will display only first and last records in the orders table which is show below: