Sqlserver uses the ranking function Row_Number () function query specifies a ran

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.

Concrete steps:

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'

from Student

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

select *

from(select StudentID, StudentName, ClassID, Row_Number() OVER(Order by StudentID) AS 'RowNumber'

          from Student

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

Posted by William at November 25, 2013 - 7:22 PM