IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

How to compare two rows of same table


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Ajay Kumar Bang

New User


Joined: 10 Feb 2023
Posts: 8
Location: Delhi

PostPosted: Fri Feb 10, 2023 8:46 pm
Reply with quote

0


I have the following table1:

ID_BOOK|MONTH_BOOK|QUALITY_BOOK|DPD
1110|201911|4|22
1110|201910|3|15
1110|201907|1|2
1117|201911|4|2
1117|201909|3|7
1117|201907|2|7
2114|201911|3|7
2114|201910|3|7
2114|201909|1|0
2114|201908|1|0
3226|201911|5|19
3226|201910|4|10
3226|201908|1|4
4555|201911|2|11
4555|201910|2|10
7888|201911|2|12

I want to achieve results by choosing ID_BOOK, MONTH_BOOK, QUALITY_BOOK AND DPD with the following provisions:


(there was an increase from the previous MONTH_BOOK)
(there was an increase in DPD from the previous MONTH_BOOK). ie:
ID_BOOK|MONTH_BOOK|QUALITY_BOOK|DPD
1110|201911|4|22
1117|201911|2|2
3226|201911|5|19
4555|201911|2|11

2114 has same QUALITY_BOOK AND DPD has no change so should not be fetched
7888 is appeared only once so even this should not be fetched
How can I achieve this?
Back to top
View user's profile Send private message
dneufarth

Active User


Joined: 27 Apr 2005
Posts: 420
Location: Inside the SPEW (Southwest Ohio, USA)

PostPosted: Fri Feb 10, 2023 9:06 pm
Reply with quote

You say you have done something. Please show what you have tried.
Back to top
View user's profile Send private message
Ajay Kumar Bang

New User


Joined: 10 Feb 2023
Posts: 8
Location: Delhi

PostPosted: Fri Feb 10, 2023 9:23 pm
Reply with quote

Actually I tried to use inner join with table alias but kind of not working, not sure if we can use Sub query.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2140
Location: USA

PostPosted: Fri Feb 10, 2023 9:26 pm
Reply with quote

Ajay Kumar Bang wrote:
Actually I tried to use inner join with table alias but kind of not working, not sure if we can use Sub query.


Please, demonstrate the sample(s) of your attempts.

And also: learn how to use the Code button to present any code/data samples.
Back to top
View user's profile Send private message
Ajay Kumar Bang

New User


Joined: 10 Feb 2023
Posts: 8
Location: Delhi

PostPosted: Fri Feb 10, 2023 11:04 pm
Reply with quote

Code:
select
    this.id_book as current_id_book,
    this.month_book as current_month_book ,
    this.quality_book as current_quality_book,
    this.dpd as current_dpd,
    old.id_book as old_id_book,
    old.month_book as old_month_book ,
    old.quality_book as old_quality_book,
    old.dpd as old_dpd
from
    TABLE1 as this
inner join TABLE1 as old on
    old.ID_BOOK = this.ID_BOOK
where
 This.QUALITY_BOOK > old.QUALITY_BOOK
        or this.DPD > old.DPD;
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2140
Location: USA

PostPosted: Fri Feb 10, 2023 11:22 pm
Reply with quote

Ajay Kumar Bang wrote:
Code:
select
    this.id_book as current_id_book,
    this.month_book as current_month_book ,
    this.quality_book as current_quality_book,
    this.dpd as current_dpd,
    old.id_book as old_id_book,
    old.month_book as old_month_book ,
    old.quality_book as old_quality_book,
    old.dpd as old_dpd
from
    TABLE1 as this
inner join TABLE1 as old on
    old.ID_BOOK = this.ID_BOOK
where
 This.QUALITY_BOOK > old.QUALITY_BOOK
        or this.DPD > old.DPD;


Quote:
(there was an increase from the previous MONTH_BOOK)
(there was an increase in DPD from the previous MONTH_BOOK)


1. Please, demonstrate the actual output you are not satisfied with?

2. There is no sign of any "previous month" verification in your code sample?

3. I usually split the complex SQL into smaller subqueries, with output of intermediate results, to find out: which of them has produced unexpected results?
Back to top
View user's profile Send private message
Ajay Kumar Bang

New User


Joined: 10 Feb 2023
Posts: 8
Location: Delhi

PostPosted: Fri Feb 10, 2023 11:37 pm
Reply with quote

1. Please, demonstrate the actual output you are not satisfied with?
getting duplicates.

2. There is no sign of any "previous month" verification in your code sample?
I want the latest record

3. I usually split the complex SQL into smaller subqueries, with output of intermediate results, to find out: which of them has produced unexpected results?
I'm new to Mainframe haven't worked on too much db2 queries. So looking for a help.
Back to top
View user's profile Send private message
Ajay Kumar Bang

New User


Joined: 10 Feb 2023
Posts: 8
Location: Delhi

PostPosted: Fri Feb 10, 2023 11:42 pm
Reply with quote

I basically want to compare last 2 records inserted in the table, for instance on 4th execution, day 4 inserted record should be compared with day 3 inserted record and day 4 should not be compared with day 2 or day 1.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2140
Location: USA

PostPosted: Sat Feb 11, 2023 12:07 am
Reply with quote

The full set of your sample records is as follows
Code:
1110   201911   4   22
1110   201910   3   15
1110   201907   1    2
1117   201911   4    2
1117   201909   3    7
1117   201907   2    7
2114   201911   3    7
2114   201910   3    7
2114   201909   1    0
2114   201908   1    0
3226   201911   5   19
3226   201910   4   10
3226   201908   1    4
4555   201911   2   11
4555   201910   2   10
7888   201911   2   12

To simplify the analysis, let's take only one single group
Code:
1110   201911   4   22
1110   201910   3   15
1110   201907   1    2

After this operation
Code:
inner join TABLE1 as old on
    old.ID_BOOK = this.ID_BOOK

The expected result must be "each-to-each":
Code:
1110   201911   4   22      1110   201911   4   22
1110   201911   4   22      1110   201910   3   15
1110   201911   4   22      1110   201907   1    2
1110   201910   3   15      1110   201911   4   22
1110   201910   3   15      1110   201910   3   15
1110   201910   3   15      1110   201907   1    2
1110   201907   1    2      1110   201911   4   22
1110   201907   1    2      1110   201910   3   15
1110   201907   1    2      1110   201907   1    2

Next, this set is truncated after applying
Code:
where
 This.QUALITY_BOOK > old.QUALITY_BOOK
        or this.DPD > old.DPD

It must produce the following:
Code:
1110   201911   4   22      1110   201910   3   15
1110   201911   4   22      1110   201907   1    2
1110   201910   3   15      1110   201911   4   22
1110   201910   3   15      1110   201907   1    2
1110   201907   1    2      1110   201911   4   22
1110   201907   1    2      1110   201910   3   15


In your code there is nothing to further extract only the required rows from this whole set of data. You need to enhance your SQL further.

Besides this, it is not clear: do you need the last WHERE clause to check for "greater than", or "not equal to"?
Back to top
View user's profile Send private message
Ajay Kumar Bang

New User


Joined: 10 Feb 2023
Posts: 8
Location: Delhi

PostPosted: Sat Feb 11, 2023 12:26 am
Reply with quote

Code:
 can I do like this?

Select a.id_book, max(a.month_book)
From table1 a. Table1 B
Where (a.quality_book - b.quality_month) > 0
Group by a.id_book
Having count(a.id_book) > 1;

But this might also compare day 3 and day 2, day 2 and Day 1.
Where as I need only day 4 and day 3.
Back to top
View user's profile Send private message
Ajay Kumar Bang

New User


Joined: 10 Feb 2023
Posts: 8
Location: Delhi

PostPosted: Sat Feb 11, 2023 12:27 am
Reply with quote

In simple I want to compare latest entry with immediate previous entry but not the older entries. Is this possible?
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2140
Location: USA

PostPosted: Sat Feb 11, 2023 1:20 am
Reply with quote

Ajay Kumar Bang wrote:
In simple I want to compare latest entry with immediate previous entry but not the older entries. Is this possible?


Please, try to use at least Google, to say nothing about professional manuals, and guides.

learnsql.com/cookbook/how-to-select-the-first-row-in-each-group-by-group/
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts JCL sort to compare dates in two file... DFSORT/ICETOOL 2
No new posts Reflection 3270 screen shows only 23 ... Compuware & Other Tools 6
No new posts Compare 2 alphabets in a REXX and fin... CLIST & REXX 2
No new posts Unload and Load ISPF Table TSO/ISPF 4
No new posts ISPF Table to add a new column TSO/ISPF 1
Search our Forums:

Back to Top