create table test_rank ( dept varchar(10) , ename varchar(10) , sal int) ;
insert into test_rank values
('dept' , 'scott100' , 100) ,
('dept' , 'scott200' , 200) ,
('dept' , 'scott300' , 300) ,
('dept' , 'scott300' , 300) ,
('aaa' , 'john100' , 100) ,
('aaa' , 'john200' , 200) ,
('aaa' , 'john300' , 300) ,
('aaa' , 'john300' , 300) ;
commit ;
select * from test_rank ;
+------+----------+------+
| dept | ename | sal |
+------+----------+------+
| dept | scott100 | 100 |
| dept | scott200 | 200 |
| dept | scott300 | 300 |
| dept | scott300 | 300 |
| aaa | john100 | 100 |
| aaa | john200 | 200 |
| aaa | john300 | 300 |
| aaa | john300 | 300 |
+------+----------+------+
SELECT rnum , dept , ename , sal
FROM ( select a.* ,
(CASE @dept WHEN a.dept THEN @rownum:=@rownum+1 ELSE @rownum:=1 END) rnum,
(@dept:=a.dept) vdept
FROM test_rank a , ( select @dept := '' , @rownum := 0 ) b
ORDER BY a.dept , a.sal desc
) main ;
+------+------+----------+------+
| rnum | dept | ename | sal |
+------+------+----------+------+
| 1 | aaa | john300 | 300 |
| 2 | aaa | john300 | 300 |
| 3 | aaa | john200 | 200 |
| 4 | aaa | john100 | 100 |
| 1 | dept | scott300 | 300 |
| 2 | dept | scott300 | 300 |
| 3 | dept | scott200 | 200 |
| 4 | dept | scott100 | 100 |
+------+------+----------+------+