6 Replies Latest reply: Feb 1, 2012 12:31 AM by 883627

# Sequence Checking

i have 2 table as like below:

table1
type     partisi     priority
A     AA     1
A     BB     2
A     CC     3
B     AA     1
B     CC     2
B     DD     3

table2
type     partisi     priority     expected result
A     AA     2     1
A     BB     1     2
A     CC     3     3
B     DD     1     2
B     CC     2     1

Based on the table1, i want to checking the priority on table2 is correct or not. Column of expected result is expected result from query.
i have no any idea for checking this case by query.

Are there can some person solve this case ?
• ###### 1. Re: Sequence Checking
I didn't really get what you are trying to do.
Is the key between the tables "type" and "partisi"?
Then what do you want to compare table1.priority to table2.expected_result?
What do you want to see, all rows where the priority is not equal to expected_result?
• ###### 2. Re: Sequence Checking
Rahardi Trisna wrote:
i have 2 table as like below:

table1
type     partisi     priority
A     AA     1
A     BB     2
A     CC     3
B     AA     1
B     CC     2
B     DD     3

table2
type     partisi     priority     expected result
A     AA     2     1
A     BB     1     2
A     CC     3     3
B     DD     1     2
B     CC     2     1

Based on the table1, i want to checking the priority on table2 is correct or not. Column of expected result is expected result from query.
i have no any idea for checking this case by query.

Are there can some person solve this case ?
if i understood u correctly, u need this
``````with a
as (
select 'A' type, 'AA' partisi, 1 priority from dual union all
select 'A', 'BB', 2 from dual  union all
select 'A', 'CC', 3 from dual union all
select 'B', 'AA', 1 from dual union all
select 'B', 'CC', 2 from dual union all
select 'B', 'DD', 3 from dual ),

b as
(select 'A' type, 'AA' partisi, 2 priority from dual union all
select 'A', 'BB', 1 from dual  union all
select 'A', 'CC', 3 from dual union all
select 'B', 'DD', 1 from dual  union all
select 'B', 'CC', 2 from dual)

select b.type,b.partisi,b.priority,a.priority expected_result
from a,b
where  a.type=b.type
and a.partisi =b.partisi``````
or elobrate the problem more clearly..
Regards

Umesh
• ###### 3. Re: Sequence Checking

TYPE     PARTISI     PRIORITY     EXPECTED_RESULT
A     AA     2     1
A     BB     1     2
A     CC     3     3
B     CC     2     2
B     DD     1     3

my expected is

TYPE     PARTISI     PRIORITY     EXPECTED_RESULT
A     AA     2     1
A     BB     1     2
A     CC     3     3
B     CC     2     *1*
B     DD     1     *2*

Sorted by priority. fixed value of 1 should be filled, so that the smallest expected result here is 2 go up to 1. the sample like this:
2-->up to become 1
4-->up to become 3
3-->up to become 2

May be my explaination make you undestand.

very thanks for all
• ###### 4. Re: Sequence Checking
hi Liron Amitzi, i just want get sequence number. the sequence number replace back to number 1 again if get difference partisi column.
• ###### 5. Re: Sequence Checking
You can use the row_number analytic function:
``````select b.type,b.partisi,b.priority,a.priority expected_result, row_number() over (partition by b.type order by b.partisi)
from a,b
where  a.type=b.type
and a.partisi =b.partisi``````
Does this help?
• ###### 6. Re: Sequence Checking
thanks Liron, you are cleaver