USING ROW_NUMBER FUNCTION IN SQL QUERY

ROW_NUMBER is used to query the row number. It's very useful when need to get the sequence number of an ordered query.
Here's the example of query to use it.

Table 'TClass'




Give row number from the most to least Num_Attendee:

Query:
SELECT ROW_NUMBER() OVER (ORDER BY Num_Attendee desc) AS Row_Num, * FROM TClass

Result:




Give row number for each Class from the most to least Num_Attendee in a class:

Query:
SELECT ROW_NUMBER() OVER (PARTITION BY CLASS ORDER BY Num_Attendee desc) AS Row_Num, * FROM TClass

Result:




Get the most Num_attendee of each class:

Query:
SELECT Class, Num_attendee FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY CLASS ORDER BY Num_Attendee desc) AS Row_Num, * FROM TClass
) x
WHERE x.Row_Num=1

Result: