I have a few to questions pose in response first:-
Is this homework/assignment? There are specific forums for these.
What have you tried so far?
What output/errors do you get?
What OS and version are you using?
What logical process have you considered? (to help steer us to follow what you are trying to achieve)
Most importantly, What have you tried so far?
There are probably many ways to achieve most tasks, so giving us an idea of your style and thoughts will help us guide you to an answer most suitable to you so you can adjust it to suit your needs in future.
We're all here to learn and getting the relevant information will help us all.
I did try to rank option but looking for better approach
Looking for other option
4.Windows 10, DB2 v9
5.NA
Well I ranked based on roles first and have stored as reference tables. Since the roles may increases and example posted above has only 2 role examples .
Just to clear - I am new to DB2 and hope to learn from this post. Between I don't need exact code but the idea or heads up would work
select * from testing ;
Ramesh|10|MANAGER
Ramesh|11|INTERVIEWER
Anand|11|INTERVIEWER
Anand|10|MANAGER
kits|11|INTERVIEWER
Peasant|12|EMPLOYEE
Terminator|5|MACHINE
Terminator|10|MANAGER
select * from testing order by (
case role_name
when 'MANAGER'
then 1
when 'INTERVIEWER'
then 2
when 'MACHINE'
then 3
when 'EMPLOYEE'
then 4
end
) asc;
Ramesh|10|MANAGER
Anand|10|MANAGER
Terminator|10|MANAGER
Ramesh|11|INTERVIEWER
Anand|11|INTERVIEWER
kits|11|INTERVIEWER
Terminator|5|MACHINE
Peasant|12|EMPLOYEE
Hello Peasant.
Thanks for the reply.
But the expectation is different. If the person contains both roles say manager and interviewer . It should show only one record with manager . Please see below expected results. If no manager then name should pick next hierarchy interviewer .
What you want are the "rank()" or "dense_rank()" analytic functions.
They could be referred to as "window functions" or "OLAP functions" in DB2 and some of them are available in DB2 9.7 at least.
Not sure if your version has it.
The queries below are in Oracle and they should work in DB2 as well.
Using the "rank()" function, I take a name ("partition by name") and order all records by role_id ("order by role_id").
So, for "Ramesh" the lowest role_id is 10 - that record has rank 1.
For "Ramesh", the next role_id is 11 - that record has rank 2.
Once the name changes, the rank is reset to 1.
Now for "KITS", the lowest role_id is 11 - so that record has rank 1.
And so on...
SQL>
SQL> select * from testing;
NAME ROLE_ID ROLE_NAME
------------------------------ ---------- ---------------
Ramesh 10 MANAGER
Ramesh 11 INTERVIEWER
KITS 11 INTERVIEWER
ANAND 10 MANAGER
ANAND 11 INTERVIEWER
5 rows selected.
SQL>
SQL> select name, role_id, role_name,
2 rank() over (partition by name order by role_id) as rnk
3 from testing
4 ;
NAME ROLE_ID ROLE_NAME RNK
------------------------------ ---------- --------------- ----------
ANAND 10 MANAGER 1
ANAND 11 INTERVIEWER 2
KITS 11 INTERVIEWER 1
Ramesh 10 MANAGER 1
Ramesh 11 INTERVIEWER 2
5 rows selected.
SQL>
SQL>
Once you have data in this form, all you have to do is to pick up records with rnk = 1 in an outer query. (You cannot pick them up in the same query in the "where" clause.)
SQL>
SQL> with t as (
2 select name, role_id, role_name,
3 rank() over (partition by name order by role_id) as rnk
4 from testing
5 )
6 select name, role_id, role_name
7 from t
8 where rnk = 1
9 order by role_id
10 ;
NAME ROLE_ID ROLE_NAME
------------------------------ ---------- ---------------
ANAND 10 MANAGER
Ramesh 10 MANAGER
KITS 11 INTERVIEWER
3 rows selected.
SQL>
SQL>
In case your DB2 version does not understand that "with" clause, then try a subquery:
SQL>
SQL> select t.name, t.role_id, t.role_name
2 from (
3 select name, role_id, role_name,
4 rank() over (partition by name order by role_id) as rnk
5 from testing
6 ) t
7 where t.rnk = 1
8 order by t.role_id
9 ;
NAME ROLE_ID ROLE_NAME
------------------------------ ---------- ---------------
ANAND 10 MANAGER
Ramesh 10 MANAGER
KITS 11 INTERVIEWER
3 rows selected.
SQL>
SQL>
(You may need to add the keyword "as" between ")" and "t" in line 6.)
And in case your DB2 version does not understand analytic functions at all, then we have to go back 10 to 15 years in time.
Back in the day, we used the good old "exists" clause and a self-join to solve this problem.
SQL>
SQL> --
SQL> -- Pick up the row from "x" for which there is no row in "y" (same table)
SQL> -- with the same name but lower role_id. That is, pick up the row from "x" with
SQL> -- the lowest role_id for each name.
SQL> --
SQL> select x.name, x.role_id, x.role_name
2 from testing x
3 where not exists (select null
4 from testing y
5 where y.name = x.name
6 and y.role_id < x.role_id
7 )
8 order by x.role_id
9 ;
NAME ROLE_ID ROLE_NAME
------------------------------ ---------- ---------------
ANAND 10 MANAGER
Ramesh 10 MANAGER
KITS 11 INTERVIEWER
3 rows selected.
SQL>
SQL>
You can immediately see how powerful analytic functions are compared to the query above. Analytic functions scan the table once; the "exists" query scans the table more than once. There are many other advantages as well. Many extremely convoluted queries from the "old" SQL can be written in a trivial manner using analytic functions of the "new" SQL.
Hello @durden_tyler
Superb !! This works perfectly in db2 v10. I like the way you elaborated - crystal clear walkthrough for this scenario handling. Thanks again for your patience .
