MySQL: Rank by Department in an employees table

Question

enter image description here

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.

score 1 · Answer 1 · edited Apr 13 '17 at 12:42

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)>0,LOCATE('0',deptname),99),
    IF(LOCATE('1',deptname)>0,LOCATE('1',deptname),99),
    IF(LOCATE('2',deptname)>0,LOCATE('2',deptname),99),
    IF(LOCATE('3',deptname)>0,LOCATE('3',deptname),99),
    IF(LOCATE('4',deptname)>0,LOCATE('4',deptname),99),
    IF(LOCATE('5',deptname)>0,LOCATE('5',deptname),99),
    IF(LOCATE('6',deptname)>0,LOCATE('6',deptname),99),
    IF(LOCATE('7',deptname)>0,LOCATE('7',deptname),99),
    IF(LOCATE('8',deptname)>0,LOCATE('8',deptname),99),
    IF(LOCATE('9',deptname)>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> use test
Database changed
mysql> DROP TABLE IF EXISTS dept;
Query OK, 0 rows affected (0.01 sec)

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

mysql> 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);
Query OK, 9 rows affected (0.01 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql>

PROPOSED SOLUTION EXECUTED

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

mysql> 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)>0,LOCATE('0',deptname),99),
    ->     IF(LOCATE('1',deptname)>0,LOCATE('1',deptname),99),
    ->     IF(LOCATE('2',deptname)>0,LOCATE('2',deptname),99),
    ->     IF(LOCATE('3',deptname)>0,LOCATE('3',deptname),99),
    ->     IF(LOCATE('4',deptname)>0,LOCATE('4',deptname),99),
    ->     IF(LOCATE('5',deptname)>0,LOCATE('5',deptname),99),
    ->     IF(LOCATE('6',deptname)>0,LOCATE('6',deptname),99),
    ->     IF(LOCATE('7',deptname)>0,LOCATE('7',deptname),99),
    ->     IF(LOCATE('8',deptname)>0,LOCATE('8',deptname),99),
    ->     IF(LOCATE('9',deptname)>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;
+------+-------------+-------+------+
| 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>

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)>0,LOCATE('0',deptname),99),
    IF(LOCATE('1',deptname)>0,LOCATE('1',deptname),99),
    IF(LOCATE('2',deptname)>0,LOCATE('2',deptname),99),
    IF(LOCATE('3',deptname)>0,LOCATE('3',deptname),99),
    IF(LOCATE('4',deptname)>0,LOCATE('4',deptname),99),
    IF(LOCATE('5',deptname)>0,LOCATE('5',deptname),99),
    IF(LOCATE('6',deptname)>0,LOCATE('6',deptname),99),
    IF(LOCATE('7',deptname)>0,LOCATE('7',deptname),99),
    IF(LOCATE('8',deptname)>0,LOCATE('8',deptname),99),
    IF(LOCATE('9',deptname)>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)>0,LOCATE('0',deptname),99),
    IF(LOCATE('1',deptname)>0,LOCATE('1',deptname),99),
    IF(LOCATE('2',deptname)>0,LOCATE('2',deptname),99),
    IF(LOCATE('3',deptname)>0,LOCATE('3',deptname),99),
    IF(LOCATE('4',deptname)>0,LOCATE('4',deptname),99),
    IF(LOCATE('5',deptname)>0,LOCATE('5',deptname),99),
    IF(LOCATE('6',deptname)>0,LOCATE('6',deptname),99),
    IF(LOCATE('7',deptname)>0,LOCATE('7',deptname),99),
    IF(LOCATE('8',deptname)>0,LOCATE('8',deptname),99),
    IF(LOCATE('9',deptname)>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.