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.
Dear Robin
1. This is just learning exercise
2. I did try to rank option but looking for better approach
3. 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
Regards
Peasant
Last edited by Peasant; 08-22-2018 at 10:07 AM..
Reason: Changed case to uppercase input.
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.
...
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...
Code:
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.)
Code:
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:
Code:
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.
Code:
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.
Cheers!
Last edited by durden_tyler; 08-27-2018 at 02:16 PM..
These 2 Users Gave Thanks to durden_tyler For This Post:
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 .
I am using DB2 v9 and trying to get country values in comma seperated format using below query
SELECT distinct LISTAGG(COUNTRIES, ',') WITHIN GROUP(ORDER BY EMPLOYEE)
FROM LOCATION ;
Output Achieved
MEXICO,UNITED STATES,INDIA,JAPAN,UNITED KINGDOM,MEXICO,UNITED STATES
The table... (4 Replies)
Hi Team
I am using DB2 artisan tool and struck to handle multi values present in columns that are comma(,) separated. I want to convert those column values in separate rows .
For example :
Column 1 Column2
Jan,Feb Hold,Sell,Buy
Expected Result
Column1 ... (3 Replies)
Hello,
i need some help with a script. I made a script, which connect to different hosts to get some informations. But i got now some problems with getting informations of a database (db2) which is on a other host. I tried something like
var=$(rsh HOST su - db2adm -c "db2 connect to database;... (2 Replies)
Hi,
I want to replace a chain of if-else statement in an old AWK file with values from Db2 table or CSV file. The part of code is below...
if (start_new_rec=="true"){
exclude_user="false";
user=toupper($6);
match(user, "XXXXX.");
if (RSTART ==2 ) {
... (9 Replies)
Hi,
Im new to DB2.
I need to connect to DB2 from shell script and return the query output back to shell script variable.
this is my code
#!/bin/ksh
db_name=db
db_user=usr
db_pwd=pwd
db2 <<EOSQL
connect to $db_name user $db_user using "$db_pwd"
select count(1) from table
quit
EOSQL ... (3 Replies)
I am new to AIX, but not new to the UNIX world. Recently I got a request to come with some kind of report to do the following: report if user query is using > 70% CPU (within DB2) > 5 minutes. I am by no means a DB2 guru so I do not know how to get that from within it, so I was wondering if there... (4 Replies)
Hi All,
I want to connect two tables in DB2 using shell script and then compare the contents of two tables field by field.and i should return on the screen the un matched records ..
Could any one please help me in connecting database tables using Unix and retriving data from the same. (1 Reply)
Hi,
I have a requirement as below which needs to be done viz UNIX shell script
(1) I have to connect to an Oracle database
(2) Exexute "SELECT field_status from table 1" query on one of the tables.
(3) Based on the result that I get from point (2), I have to update another table in the... (6 Replies)
Hi ,
I am using a db2 query to get the maximum sequence number from a field.Now this value is taking up only 2 places.(i.e its less than 100).It can grow up to 3 places later but me requirement is that when i write it in a text file, it should be padded upto 8 places.
Please help (1 Reply)
Dear Guyz:)
I have 2 different input files like this. I would like to pick the values or letters from the inputfile2 based on inputfile1 keys (A,F,N,X,Z).
I have done similar task by using awk but in that case the inputfiles are similar like in inputfile2 (all keys in 1st column and values in... (16 Replies)
08-23-2018
