1

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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.