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

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,


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

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

Senior Member

Joined: 29 Apr 2008
Posts: 1699

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 To search DB2 table based on Conditio... DB2 1
No new posts How to compare two rows of same table DB2 11
This topic is locked: you cannot edit posts or make replies. Compare files with duplicates in one ... DFSORT/ICETOOL 11
No new posts how to send 10 rows at a time to output CICS 2
No new posts Read a flat file and update DB2 table JCL & VSAM 2
Search our Forums:

Back to Top