Forum Stats

  • 3,838,564 Users
  • 2,262,383 Discussions


delegation query based on levels

Gor_Mahia Member Posts: 1,147 Bronze Badge
edited Jul 30, 2022 10:36AM in SQL & PL/SQL


iam writing a delegation process based on dept and rank/role level with level 1 being highest and level 4 lowest. 

So if the current person is level 1 then take level 2(if exist) for that dept.

for instance in the below if the current user is scott from sales then return Mary and Jack from sales with level 2, 

if the current user is owen from HR then return Oloo, mark and otieno with level 3. But if the current user is Oloo from HR return Kumo from HR with level 4.

so generally for a given user always the query should return the next lower level in the table.

username  dept    level


scott     sales  1

mary    sales   2

Jack sales   2

jane     sales   3

kelly     sales    4

owen    hr     1

oloo     hr     3

mark hr     3

otieno    hr     3

kumo   hr      4

Indy     IT     2

Marwa    IT     4

any idea on a good query to return the values?

still using Oracle 11.2g




  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,243 Red Diamond
    edited Jul 30, 2022 10:45AM

    Hi, @Gor_Mahia

    Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements for all tables involved, relevant columns only) so the people who want to help you can re-create the problem and test their ideas. Also post the exact results you want from that data, and explain why you want those results from that data, including special cases (e.g. dept 'IT' in your sample data, where level has values 2 and 4, but not 3). Always post your complete Oracle version (e.g.

    Perhaps you want something like this:

    SELECT    c.*
    ,         l.username    AS delegate
    FROM      table_x  c  -- C is for Current
    JOIN      table_x  l  -- L is for Lower Level
              ON  l.dept  = c.dept
    AND       l.lvl   = c.lvl + 1   -- LEVEL is an Oracle keyword, not a good column name
    ORDER BY  c..dept, c.lvl, c.username  -- or whatever you want

    This produces one row for every higher pair of people in a higher level/lower level relationship. If you want one row for every person, then you can do the same thing, but use LISTAGG.