• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 369
  • Last Modified:

DB2 - SELECT LOWEST GROUP

I have the following tables:

SEC_LEVEL_ID SEC_LEVEL_GROUP SEC_LEVEL_LEVEL SEC_LEVEL_DESC	
1	    1	                 1	                             ABC	
2	    1	                 2	                             DEF	
3	    1	                 3	                             GHI
4	    2	                 1	                             XYZ	
5	    3	                 1	                             STU	

MAIN_TABLE

NAME       SEC_LEVEL_ID
JASON     2
JASON     3
JASON     5
SIRI           1
SIRI           2
SIRI           4
FRANK     1
FRANK     4
FRANK     5

Open in new window


The hard part is if the SEC_LEVEL_IDs are in the same SEC_LEVEL_GROUP, we only display the lowest SEC_LEVEL_LEVEL.

In this case, the result should be

JASON      DEF
JASON      STU
SIRI           ABC
SIRI           XYZ
FRANK     ABC
FRANK     XYZ
FRANK    STU

Thank you very much for your help
0
ttta83
Asked:
ttta83
  • 4
  • 4
1 Solution
 
momi_sabagCommented:
select name, sec_level_desc
from (select t1.name, t2.sec_level_desc, row_numbe() over(partition by t2.sec_level_id order by sec_level_level ) rown
   from main_table t1 join sec_table t2 on t1.sec_level_id = t2.sec_level_id
)
where rown=1
0
 
ttta83Author Commented:
momi_sabag,

Sorry, below is the full layout.  There are 3 tables.  I tried to make my question simple.  Would you please help me?
I'm trying to get the lowest SEC_LEVEL_LEVEL for each SEC_LEVEL_GROUP IN TABLE SEC_LEVEL

TABLE SEC_LEVEL

SEC_LEVEL_ID SEC_LEVEL_GROUP SEC_LEVEL_LEVEL SEC_LEVEL_DESC	
1	    1	                 1	                             ABC	
2	    1	                 2	                             DEF	
3	    1	                 3	                             GHI
4	    2	                 1	                             XYZ	
5	    3	                 1	                             STU

TABLE SEC_LEVEL_REQMT
SEC_LEVEL_ID     SEC_REQMT_ID
1                           2
1                           3
2                           4
2                           5
2                           7
2                           8
3                           17
3                           18
4                           1
5                           9
5                           10

TABLE MAIN_SEC_REQMT
MAIN_ID  SEC_REQMT
1             4
1             5
1             8
1             17     
2             2
2             8
2             18
2             1

Open in new window


The result should be

MAIN_ID   SEC_LEVEL_DESC
1              DEF
2              ABC
2              XYZ
0
 
momi_sabagCommented:
try

select main_id, sec_level_desc
from (
   select   t1.main_id, t3. sec_level_desc, row_number() over(partition by t3.sec_level_group order by sec_level_level) rown
   from     main_sec_reqmt t1
             join sec_level_reqmt t2 on t1.sec_reqmt = t2.sec_reqmt_id
                join sec_level t3 on t2.sec_level_id = t3.sec_level_id
)
where rown=1
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
ttta83Author Commented:
doesn't work... still display all sec_level's

MAIN_ID   SEC_LEVEL_DESC
1              DEF
1              DEF
1              DEF
1              GHI
0
 
momi_sabagCommented:
try

select main_id, sec_level_desc
from (
   select   t1.main_id, t3. sec_level_desc, row_number() over(partition by t1.main_id, order by sec_level_level) rown
   from     main_sec_reqmt t1
             join sec_level_reqmt t2 on t1.sec_reqmt = t2.sec_reqmt_id
                join sec_level t3 on t2.sec_level_id = t3.sec_level_id
)
where rown=1
0
 
ttta83Author Commented:
it looks better :)  there's one problem though

For exampl MAIN_ID 2 has SEC_REQMT = 1 (SEC_LEVEL_ID = 4; SEC_LEVEL_DESC = 'XYZ')

the SQL skips this.  It only displays ABC
Thanks
0
 
momi_sabagCommented:
its hard to perfect it without having the data
the purpose was to get in the right direction

basically what you want to do is generate row numbers with the row_number function
the partition by dictates when the number generation starts, so for example, in the above query, for each new main_id
the order by dictates the order of number generation

does it make sense?


maybe this will work

try

select main_id, sec_level_desc
from (
   select   t1.main_id, t3. sec_level_desc, row_number() over(partition by t1.main_id, t3.SEC_LEVEL_GROUP  order by sec_level_level) rown
   from     main_sec_reqmt t1
             join sec_level_reqmt t2 on t1.sec_reqmt = t2.sec_reqmt_id
                join sec_level t3 on t2.sec_level_id = t3.sec_level_id
)
where rown=1
0
 
ttta83Author Commented:
yes, it works

Thank you very much for your help
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now