dept_id, salary --------------- 10, 10000 10, 20000 10, 20000 10, 30000 20, 50000 20, 60000
Now I want to group the department id and then generate the row number values on salary in ascending order.The output should look as
dept_id, salary, RowNumber -------------------------- 10, 10000, 1 10, 20000, 2 10, 20000, 3 10, 30000, 4 20, 50000, 1 20, 60000, 2
1. Just for your refernce, I am providing the sql query in oracle to generate row numbers.
SELECT dept_id, salary, row_number() over ( partition by dept_id order by salary ) RowNumber FROM emp;
2. Let see how to calculate the row numbers in mysql sql queries. Mysql supports using of variables in sql queries. Now we use the variable support to generate the row numbers (sequence values). The following query generates the row number values grouping on dept_id and sorting on salary:
set @pk1 =''; set @rn1 =1; SELECT dept_id, salary, rowNumber FROM ( SELECT dept_id, salary, @rn1 := if(@pk1=dept_id, @rn1+1,1) as rowNumber, @pk1 := dept_id FROM ( SELECT dept_id, salary FROM emp ORDER BY dept_id,salary ) A ) B;
Understanding the Row Number Mysql Sql Query:
- Declare two variables using the set operator. The pk1 variable is to track the changes in department id and the ran1 variable is to calculate the rank.
- Sort the rows on the grouped columns and then the ranking columns. Here grouped column is dept_id and ranking column is salary. This order by should be done in inner query.
- In the outer query, compare whether the current row department id with previous row department id. If the department id is newer one or changes, then assign rn1 with 1, otherwise increment it by 1. The previous department id value is stored in the pk1 variable.
- Finally select the required columns in the outermost query.