There is a table in the database field Student, StudentID, StudentName, ClassID, where StudentID is not consecutively, the task now is to retrieve the specified range of data, such as the query to the fifth data and tenth data. If StudentID is a serial number, then the task will be easier. For discontinuous number, we can use the Row_Number function, the data rows using the function, and then be sorted according to the number of data generated by screening.
1 first use the Row_Number function to add the sort of data
select StudentID, StudentName, ClassID, Row_Number() OVER(Order by StudentID) AS 'RowNumber'
Note that the Row_Number function here must have the Order By field, Partition By fields are optional. After this step is complete, will add a column of “ RowNumber”, continuous.
2 use the first step in creating a table as the base table, combined with Between..And.. Function to the completion of the specified range of data query
from(select StudentID, StudentName, ClassID, Row_Number() OVER(Order by StudentID) AS 'RowNumber'
) as OrderStudent
where RowNumber between 5 and 10
Through this step will query through the ranks after the fifth to tenth (including fifth and tenth) data.
According to the above analysis, we can easily extend, if I want to query for each group before N data line: pattition by field using a Row_number packet, the data and rank, select each ranking data elements can be less than N.
The SQL statement specific to:
select * from (select *,Row_Number() OVER (Partiton By ClassID Order by StudentID) as "RowNumber") as OrderedData
where RowNumber <N
The first packet of data table and ranking for each group, and then select each group ranked less than the specified N.