MySQL: Rank by Department in an employees table I need Mysql query to rank the hours used by each department in an employees table, but it just ranks all without taking into consideration the departments.
PROPOSED SOLUTION

SET @currhash = MD5('dummy');
SELECT A.id,B.deptname,B.hours,A.rank
FROM (SELECT
    (@prevhash := @currhash),
    (@currhash := MD5(depttype)),
    (@rnk:=IF(@prevhash=@currhash,@rnk+1,1)) rank,
    id,depttype FROM
    (SELECT id,LEFT(deptname,LEAST(
    IF(LOCATE('0',deptname)&gt;0,LOCATE('0',deptname),99),
    IF(LOCATE('1',deptname)&gt;0,LOCATE('1',deptname),99),
    IF(LOCATE('2',deptname)&gt;0,LOCATE('2',deptname),99),
    IF(LOCATE('3',deptname)&gt;0,LOCATE('3',deptname),99),
    IF(LOCATE('4',deptname)&gt;0,LOCATE('4',deptname),99),
    IF(LOCATE('5',deptname)&gt;0,LOCATE('5',deptname),99),
    IF(LOCATE('6',deptname)&gt;0,LOCATE('6',deptname),99),
    IF(LOCATE('7',deptname)&gt;0,LOCATE('7',deptname),99),
    IF(LOCATE('8',deptname)&gt;0,LOCATE('8',deptname),99),
    IF(LOCATE('9',deptname)&gt;0,LOCATE('9',deptname),99))-1) depttype
FROM dept ORDER BY depttype,hours DESC) AA) A
LEFT JOIN dept B USING (id)
ORDER BY id;


YOUR SAMPLE DATA

use test
DROP TABLE IF EXISTS dept;
CREATE TABLE dept
(
    id int not null,
    deptname varchar(32),
    hours INT NOT NULL,
    PRIMARY KEY (id)
);
INSERT INTO dept VALUES
(1003,'sales1'     ,  2),(1004,'sales2'     ,  6),(1005,'sales3     ',  4),
(1006,'sales4'     ,  3),(1007,'operations1',  5),(1008,'operations2', 10),
(1009,'operations3',  7),(1010,'operations4',  8),(1011,'operations5',  4);


YOUR SAMPLE DATA LOADED

mysql&gt; use test
Database changed
mysql&gt; DROP TABLE IF EXISTS dept;
Query OK, 0 rows affected (0.01 sec)

mysql&gt; CREATE TABLE dept
    -&gt; (
    -&gt;     id int not null,
    -&gt;     deptname varchar(32),
    -&gt;     hours INT NOT NULL,
    -&gt;     PRIMARY KEY (id)
    -&gt; );
Query OK, 0 rows affected (0.02 sec)

mysql&gt; INSERT INTO dept VALUES
    -&gt; (1003,'sales1'     ,  2),(1004,'sales2'     ,  6),(1005,'sales3     ',  4),
    -&gt; (1006,'sales4'     ,  3),(1007,'operations1',  5),(1008,'operations2', 10),
    -&gt; (1009,'operations3',  7),(1010,'operations4',  8),(1011,'operations5',  4);
Query OK, 9 rows affected (0.01 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql&gt;


PROPOSED SOLUTION EXECUTED

mysql&gt; SET @currhash = MD5('dummy');
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SELECT A.id,B.deptname,B.hours,A.rank
    -&gt; FROM (SELECT
    -&gt;     (@prevhash := @currhash),
    -&gt;     (@currhash := MD5(depttype)),
    -&gt;     (@rnk:=IF(@prevhash=@currhash,@rnk+1,1)) rank,
    -&gt;     id,depttype FROM
    -&gt;     (SELECT id,LEFT(deptname,LEAST(
    -&gt;     IF(LOCATE('0',deptname)&gt;0,LOCATE('0',deptname),99),
    -&gt;     IF(LOCATE('1',deptname)&gt;0,LOCATE('1',deptname),99),
    -&gt;     IF(LOCATE('2',deptname)&gt;0,LOCATE('2',deptname),99),
    -&gt;     IF(LOCATE('3',deptname)&gt;0,LOCATE('3',deptname),99),
    -&gt;     IF(LOCATE('4',deptname)&gt;0,LOCATE('4',deptname),99),
    -&gt;     IF(LOCATE('5',deptname)&gt;0,LOCATE('5',deptname),99),
    -&gt;     IF(LOCATE('6',deptname)&gt;0,LOCATE('6',deptname),99),
    -&gt;     IF(LOCATE('7',deptname)&gt;0,LOCATE('7',deptname),99),
    -&gt;     IF(LOCATE('8',deptname)&gt;0,LOCATE('8',deptname),99),
    -&gt;     IF(LOCATE('9',deptname)&gt;0,LOCATE('9',deptname),99))-1) depttype
    -&gt; FROM dept ORDER BY depttype,hours DESC) AA) A
    -&gt; LEFT JOIN dept B USING (id)
    -&gt; ORDER BY id;
+------+-------------+-------+------+
| id   | deptname    | hours | rank |
+------+-------------+-------+------+
| 1003 | sales1      |     2 |    4 |
| 1004 | sales2      |     6 |    1 |
| 1005 | sales3      |     4 |    2 |
| 1006 | sales4      |     3 |    3 |
| 1007 | operations1 |     5 |    4 |
| 1008 | operations2 |    10 |    1 |
| 1009 | operations3 |     7 |    3 |
| 1010 | operations4 |     8 |    2 |
| 1011 | operations5 |     4 |    5 |
+------+-------------+-------+------+
9 rows in set (0.00 sec)

mysql&gt;


PROPOSED SOLUTION FOR TIES (BONUS)

SET @currhash = MD5('dummy');
SET @curhours = -1;
SET @rank = 0;
SET @rnk = 0;
SELECT A.id,B.deptname,B.hours,A.rank
FROM (SELECT
    (@prevhash := @currhash),
    (@currhash := MD5(depttype)),
    (@curhours := IF(@prevhash=@currhash,@curhours,-1)),
    (@rnk      := IF(@prevhash=@currhash,@rnk+1,1)),
    (@rank     := IF(@curhours=hours,@rank,@rnk)) rank,
    (@curhours := hours),
    id,depttype FROM
    (SELECT id,LEFT(deptname,LEAST(
    IF(LOCATE('0',deptname)&gt;0,LOCATE('0',deptname),99),
    IF(LOCATE('1',deptname)&gt;0,LOCATE('1',deptname),99),
    IF(LOCATE('2',deptname)&gt;0,LOCATE('2',deptname),99),
    IF(LOCATE('3',deptname)&gt;0,LOCATE('3',deptname),99),
    IF(LOCATE('4',deptname)&gt;0,LOCATE('4',deptname),99),
    IF(LOCATE('5',deptname)&gt;0,LOCATE('5',deptname),99),
    IF(LOCATE('6',deptname)&gt;0,LOCATE('6',deptname),99),
    IF(LOCATE('7',deptname)&gt;0,LOCATE('7',deptname),99),
    IF(LOCATE('8',deptname)&gt;0,LOCATE('8',deptname),99),
    IF(LOCATE('9',deptname)&gt;0,LOCATE('9',deptname),99))-1) depttype,hours
FROM dept ORDER BY depttype,hours DESC) AA) A
LEFT JOIN dept B USING (id)
ORDER BY id;


GIVE IT A TRY !!!



NOTE : Why this works

All the department names are unique. I needed to strip off the digits on the far right. 

I did that with the innermost subquery

SELECT id,LEFT(deptname,LEAST(
    IF(LOCATE('0',deptname)&gt;0,LOCATE('0',deptname),99),
    IF(LOCATE('1',deptname)&gt;0,LOCATE('1',deptname),99),
    IF(LOCATE('2',deptname)&gt;0,LOCATE('2',deptname),99),
    IF(LOCATE('3',deptname)&gt;0,LOCATE('3',deptname),99),
    IF(LOCATE('4',deptname)&gt;0,LOCATE('4',deptname),99),
    IF(LOCATE('5',deptname)&gt;0,LOCATE('5',deptname),99),
    IF(LOCATE('6',deptname)&gt;0,LOCATE('6',deptname),99),
    IF(LOCATE('7',deptname)&gt;0,LOCATE('7',deptname),99),
    IF(LOCATE('8',deptname)&gt;0,LOCATE('8',deptname),99),
    IF(LOCATE('9',deptname)&gt;0,LOCATE('9',deptname),99))-1) depttype
FROM dept ORDER BY depttype,hours DESC) AA


I designed it so that it will strip everything to the right of the first digit in the deptname. That will handle two-digit or three-digit numbers in deptname. After doing so, you end with two distinct values: sales and operations. I used this as the department type, which is aliased as depttype.

I could then iterate using local variables on the current depttype. I would also reset the ranking back to 1 when the depttype changes while iterating. The ranking technique I used is borrowed from the algorithm that I posted in Get the rank of a user in a score table. I implemented it to mechanically group by depttype and rank by hours.