Example of emulation of rank function with arrayEnumerateUniq.

rank.txt

milovidov@milovidov-xenial:~$ clickhouse-client 
ClickHouse client version 1.1.53996.
Connecting to localhost:9000.
Connected to ClickHouse server version 1.1.53996.

:) CREATE TABLE IF NOT EXISTS `employee` (
:-]   `empid` Int32,
:-]   `deptid` Int32,
:-]   `salary` Int64
:-] ) ENGINE = Memory;

CREATE TABLE IF NOT EXISTS employee
(
    empid Int32, 
    deptid Int32, 
    salary Int64
) ENGINE = Memory

Ok.

0 rows in set. Elapsed: 0.058 sec. 

:) 
:) INSERT INTO `employee` (`empid`, `deptid`, `salary`) VALUES
:-] (1, 10, 5500000),
:-] (2, 10, 4500000),
:-] (3, 20, 1900000),
:-] (4, 20, 4800000),
:-] (5, 40, 6500000),
:-] (6, 40, 14500000),
:-] (7, 40, 44500000),
:-] (8, 50, 6500000),
:-] (9, 50, 7500000);

INSERT INTO employee (empid, deptid, salary) VALUES

Ok.

:) SELECT * FROM employee ORDER BY deptid, salary DESC       

SELECT *
FROM employee
ORDER BY 
    deptid ASC, 
    salary DESC

┌─empid─┬─deptid─┬───salary─┐
│     1 │     10 │  5500000 │
│     2 │     10 │  4500000 │
│     4 │     20 │  4800000 │
│     3 │     20 │  1900000 │
│     7 │     40 │ 44500000 │
│     6 │     40 │ 14500000 │
│     5 │     40 │  6500000 │
│     9 │     50 │  7500000 │
│     8 │     50 │  6500000 │
└───────┴────────┴──────────┘

9 rows in set. Elapsed: 0.002 sec.

:) SELECT empid, deptid, salary, rank FROM (SELECT groupArray(empid) AS empids, groupArray(deptid) AS deptids, groupArray(salary) AS salaries FROM (SELECT * FROM employee ORDER BY deptid ASC, salary DESC)) ARRAY JOIN empids AS empid, deptids AS deptid, salaries AS salary, arrayEnumerateUniq(deptids) AS rank

SELECT 
    empid, 
    deptid, 
    salary, 
    rank
FROM 
(
    SELECT 
        groupArray(empid) AS empids, 
        groupArray(deptid) AS deptids, 
        groupArray(salary) AS salaries
    FROM 
    (
        SELECT *
        FROM employee
        ORDER BY 
            deptid ASC, 
            salary DESC
    )
)
ARRAY JOIN 
    empids AS empid, 
    deptids AS deptid, 
    salaries AS salary, 
    arrayEnumerateUniq(deptids) AS rank

┌─empid─┬─deptid─┬───salary─┬─rank─┐
│     1 │     10 │  5500000 │    1 │
│     2 │     10 │  4500000 │    2 │
│     4 │     20 │  4800000 │    1 │
│     3 │     20 │  1900000 │    2 │
│     7 │     40 │ 44500000 │    1 │
│     6 │     40 │ 14500000 │    2 │
│     5 │     40 │  6500000 │    3 │
│     9 │     50 │  7500000 │    1 │
│     8 │     50 │  6500000 │    2 │
└───────┴────────┴──────────┴──────┘

9 rows in set. Elapsed: 0.004 sec.