All,
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
thanks