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

Compare latest 2 rows of a table using group by


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: Sat Feb 11, 2023 3:47 pm
Reply with quote

Code:
ID  |   NAME  |  UNO | RATE
123|JANE|A3|20     ‐------- Day 3
123|JANE|A2|10.     -------Day 2
123|JANE|A1|3       ------Day 1
124|ABCD|B3|0        -------Day 3
124|ABCD|B2|10      ------Day 2
124|ABCD|B1|2       -------- Day 1


Expected result,

123|JANE|A3|20

I want to group by id and name, Order by UNO desc, having count (ID) > 1.
But with this I also want to find difference between 123-A3(Day 3) with 123-A2 (Day 2) on RATE if it's greater than 5.

I want to compare only Day 3 and Day 2 and not Day 2 and Day 1.
So I should not get
123|JANE|A2|10   or
124|ABCD|B2|10

Similarly when my job runs on Day 4 it should compare Day 4 and Day 3 only basically last 2 records from the table.should be compared.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2081
Location: USA

PostPosted: Sat Feb 11, 2023 6:34 pm
Reply with quote

This question is a part of your own parallel question.

There is a reference to a ready-to-use solution presented in that topic. The only thing you need to change is: instead of choosing one single row (= 1) you need to choose two of them (<= 2).

That’s it.

No need to open a separate topic to ask about each particular arithmetic operation.
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 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
No new posts Inserting into table while open selec... DB2 1
No new posts Compare 2 files and retrive records f... DFSORT/ICETOOL 3
Search our Forums:

Back to Top