/*---------------------------------------------*/
/* Another way to assign row numbers in MySQL  */
/*---------------------------------------------*/

/*-----------------------------------*/
/* Assigning a row numbers in MySql  */
/*-----------------------------------*/
SET @rownum := 0;
SELECT (@rownum := @rownum+1) as row_num, s.*, c.*
FROM   student s JOIN class c ON ssn=stu_ssn
ORDER BY lname;

/*-----------------------------------------------------*/
/* Assigning a row numbers in MySql, without using SET */
/*-----------------------------------------------------*/
SELECT *                                              
FROM        (select @rownum:=0 as "rownum") AS init_rownum 
CROSS JOIN  (select @rownum:=@rownum+1 as "rownum", vendor, description, amount
               from payment 
              order by amount desc) AS source; 

/*--------------------------------------------*/
/* Assigning a row numbers after aggregation  */
/*--------------------------------------------*/
SET @rownum := 0;
SELECT @rownum:=@rownum+1 as row_num, subq.*
FROM (
    SELECT vendor, sum(amount)
    FROM payment
    GROUP by vendor
    ORDER BY sum(amount) desc
) subq;

/*--------------------------------------------------*/
/* Assigning and filtering by row numbers in MySql  */
/*--------------------------------------------------*/
SET @rownum := 0;
SELECT *
FROM (
    SELECT (@rownum := @rownum+1) as row_num, s.*, c.*
    FROM   student s JOIN class c ON ssn=stu_ssn
    ORDER BY lname
) subq
WHERE row_num BETWEEN 5 and 10;