Get topN records

Read(451) Label: top, limit, offset,

Syntax:

SELECT TOP  n  select_list

FROM  T

 

Get top n records

FROM  T

Get top n records from each group according to order by when the statement contains by keyword and then concatenate result records; do not support union for the time being; default value of n is 1 and get the record containing the smallest/largest value of the specified field from each group according to order by

SELECT  select_list

FROM  T  LIMIT n OFFSET m

 

Skip m records to get n records

 

Example:

SELECT  top 10 EmpID,Name,Title,BirthDate ,HireDate,Boss,HomeCity

FROM Employee  ORDER BY BirthDate

 

Sort by BIRTHDAY in ascending order and get information of first 10 eldest employees

SELECT CustomerID,ID,Date,Amount,SellerID

FROM ReturnedPmt  ORDER BY Amount LIMIT 20 OFFSET 10

Sort by Amount in ascending order and get records whose amounts are the lowest from the 20th to the 30th

SELECT top * FROM ReturnedPmt  BY CustomerID order by Amount desc

Group records by CustomerID, get the record having the largest Amount value and concatenate the eligible records