SQL returns the first n rows

Scenario: Returns the last 3 orders per customer.

Assuming we already have a POC index (see http://www.cnblogs.com/xiaopotian/p/6821502.html for details), there are two strategies to accomplish this: one is to use the Row_number function Another way to use the Apply operator and Offset/fetch or top is to determine which strategy is more efficient by the density of the partitioning column (CustID). Low density-means that there are a large number of different customers, each with small orders – the solution based on the Row_number function is the best.

 withC as(SelectCustid,orderdate,orderid,empid, row_number () Over(Partition byCustIDOrder  byOrderDatedesc, OrderIDdesc) asrownum fromsales.orders)Select *  fromCwhereRowNum<=3 Order  byCustid,rownum

When the partitioning column has high density-a small number of different customers, each customer has a large number of orders, with the Apply operator for each customer calls with Offset/fetch or top query

SelectC.custid,a.*  fromSales.customers asC CrossApplySelectOrderdate,orderid,empid fromSales.orders asOwhereO.custid=C.custidOrder  byOrderDatedesc, OrderIDdescOFFSET0RowsFETCHFirst3ROWS only) asA

Both of these strategies require a POC index to work well

SQL returns the first n rows