0

Example you have the following table

CREATE TABLE emp (id,name,salary,manager)
AS
  VALUES
    ( 1  , 'james' ,  10000 , null ),
    ( 2  , 'alex'  ,   5000 , 1 ),
    ( 3  , 'Alice' ,   4500 , 1 ),
    ( 4  , 'Jone'  ,   3000 , 3 ),
    ( 5  , 'Omar'  ,   2200 , 2 );

Anyone who is a manager of your manager is also your indirect manager so Omar has Alex and James as indirect managers. I need a query that will get me the first indirect manager who has twice salary or more than the employee.

So result should be:

ID | Manager
1  | null
2  | 1
3  | 1
4  | 1
5  | 2
3
  • How many levels can exists? – McNets Jun 8 '17 at 14:04
  • 1
    Why do you say "Indirect Manager" when your desired result has 5|2, emp.id 5's direct manager is 2. Also, why is the question "greatest salary" but the body is "first indirect manager who has twice salary" – Evan Carroll Jun 8 '17 at 16:06
  • ID=1 should not be there it doesn't match both conditions. – McNets Jun 8 '17 at 18:12
1

This is a query you can try. Sure it can be simplified but is a good start I think.
The key is to traverse all the managers chain recursively retaining salary information in order to compare later.

with recursive t1 as ( -- Traverse emps all the way up in the manager chain
  select id, name, salary, manager, salary indsalary, manager indirect, 1 as level
  from emp 
  union all
    select t1.id, t1.name, t1.salary, e.manager, e.salary, t1.manager, t1.level + 1
    from emp e, t1 
    where t1.manager = e.id
)
, t2 as ( -- Check salary condition
  select *
  from t1
  where  indsalary >= 2 * salary
)
, t3 as ( -- get the row numbers 
  select t2.*, row_number() over (partition by id order by level) rn  
  from t2
)
, t4 as ( -- retain only first row per emp
  select * from t3
  where rn = 1
)
-- Add emps that has no manager or don't fullfill the salary condition
select e.id, t4.indirect manager
from emp e
left join t4
  on (e.id = t4.id);

Result:

 id | manager 
----+---------
  1 |  [null]
  2 |       1
  3 |       1
  4 |       1
  5 |       2
(5 rows)
1
  • I hope you stick around and learn a bit more. This is wayy complex and hard to follow. T3/T4 should be distinct on. The salary condition should be folded into the recursive query. dba.stackexchange.com/a/175801/2639 – Evan Carroll Jun 8 '17 at 19:10
1

Using a recursive function and LATERAL join

Next function returns last indirect manager given a employer id.

CREATE OR REPLACE FUNCTION fnManager(pid int)
  RETURNS SETOF employees AS
$BD$
DECLARE
  emp_id int;
  manager_id int;
BEGIN
  SELECT id, manager FROM employees WHERE id = $1
  INTO emp_id, manager_id;

  WHILE manager_id IS NOT NULL LOOP
      SELECT id, manager FROM employees WHERE id = manager_id
      INTO emp_id, manager_id;
  END LOOP;

  RETURN QUERY SELECT * FROM employees WHERE id = emp_id;
  RETURN;
END
$BD$
LANGUAGE 'plpgsql';

Then you can use this function with a LATERAL join to get last indirect manager of each employee, filtering by salary.

select emp.id, emp.name, emp.salary, x.id as manager_id , x.name as manager_name, x.salary as manager_salary
from employees emp
join lateral fnManager(emp.id) x ON x.salary >= (emp.salary * 2)
;
id | name  | salary | manager_id | manager_name | manager_salary
-: | :---- | -----: | ---------: | :----------- | -------------:
 2 | Alex  |   5000 |          1 | James        |          10000
 3 | Alice |   4500 |          1 | James        |          10000
 4 | John  |   3000 |          1 | James        |          10000
 5 | Omar  |   2200 |          1 | James        |          10000

dbfiddle here

0

I did something similar for SQL Server. You can take guidelines from this snippet(it has parameterized name) :

create table #emp(
id int,
Name varchar(50),
Salary int,
ReportsTo int
);

insert into  #emp
values
(1,'John',10000,null),
(2,'Annie',5000,1),
(3,'Bob',4500,1),
(4,'Dong',3000,3),
(5,'Omar',2200,2)

--select * from #emp;

with rec as (
    select #emp.ReportsTo, #emp.id, #emp.Name, #emp.Salary, 1 as level from #emp where Name = 'Omar'
    union all
    select  #emp.ReportsTo, #emp.id, #emp.Name, #emp.Salary, level + 1 as level from #emp    
    inner join rec   
    on #emp.id = rec.ReportsTo
)
select  id, ReportsTo, Name as 'Manager Name',Salary, level from rec
where level = (select min(level) from rec 
               where salary not in (select distinct top 1 salary 
                                    from rec order by salary) 
               )
OPTION (MAXRECURSION 0)
1
  • I think you can make this a bit easier but not using a subselect min for level. – Evan Carroll Jun 8 '17 at 15:09
0

PostgreSQL Recursive Table w/ Running Total

I think something like this is the fastest solution to get what you want.

WITH RECURSIVE t(id,salary,manid,runningsalary,tree) AS (
  SELECT id, salary, manager, 0, ARRAY[id]::int[]
  FROM emp
  UNION ALL
    SELECT t.id, t.salary, emp.manager, t.runningsalary + emp.salary, emp.id||tree
    FROM t
    INNER JOIN emp
      ON t.manid = emp.id
      AND t.runningsalary < 2*t.salary
)
SELECT DISTINCT ON (id)
  id,
  CASE WHEN runningsalary > salary THEN tree[1] END AS manager
FROM t
ORDER BY id, tree;
 id | manager 
----+------
  1 |     
  2 |    1
  3 |    1
  4 |    1
  5 |    2
(5 rows)

This generates a recursive table underneath like this,

SELECT *
FROM t
ORDER BY id, tree;
 id | salary | manid | runningsalary |  tree   
----+--------+-------+---------------+---------
  1 |  10000 |       |             0 | {1}
  2 |   5000 |       |         10000 | {1,2}
  2 |   5000 |     1 |             0 | {2}
  3 |   4500 |       |         10000 | {1,3}
  3 |   4500 |     1 |             0 | {3}
  4 |   3000 |       |         14500 | {1,3,4}
  4 |   3000 |     1 |          4500 | {3,4}
  4 |   3000 |     3 |             0 | {4}
  5 |   2200 |     1 |          5000 | {2,5}
  5 |   2200 |     2 |             0 | {5}
(10 rows)

manid is used for the recursion here, you can ignore it for your purposes. We're building a tree recursing with two edge conditions:

  1. When that runningsalary exceeds twice the salary we stop recursing. That's our edge condition: t.runningsalary < 2*t.salary
  2. When we no longer have a manid (manager's id) we stop recursing (nature of INNER JOIN)

With the tabled ordered like this, we see if the runningsalary is 2x the salary, if it is we take the first id in the tree (the id to trigger the edge condition). If it's not we display null.

Then we use DISTINCT ON which is a PostgreSQL extension to grab only one row per distinct column. The one with the longest tree. We don't care about the intermediary managers that make less. We only care about the last one, or the one that triggered the edge condition t.runningsalary < 2*t.salary.

As a side note, you can replace the tree with a smallint level or the like and add another column for the current empid but I prefer the added verbosity of the array-representation of recursion. Especially for demonstration purposes.

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.