Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How to find the latest record

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
chrakesh

New User


Joined: 03 May 2010
Posts: 16
Location: india

PostPosted: Mon May 10, 2010 5:08 pm    Post subject: How to find the latest record
Reply with quote

Hi guys can anyone tell me about this and what query we need to use....

Example: The latest NIK with the same country and supplier, in this example is “AC”
Part
------------------
12345678AA
12345678AB
12345678AC


The above statement to identify the latest part NIK level. (i.e.) The first 8 character should be same and maximum of 9th and 10th character
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Mon May 10, 2010 5:11 pm    Post subject: Reply to: How to find the latest record
Reply with quote

MAX function ???
Back to top
View user's profile Send private message
chrakesh

New User


Joined: 03 May 2010
Posts: 16
Location: india

PostPosted: Mon May 10, 2010 6:15 pm    Post subject:
Reply with quote

yes,finding which is max. and the latest one...
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon May 10, 2010 6:28 pm    Post subject:
Reply with quote

MAX Aggregate function!!
Back to top
View user's profile Send private message
chrakesh

New User


Joined: 03 May 2010
Posts: 16
Location: india

PostPosted: Mon May 10, 2010 8:14 pm    Post subject: Reply to: How to find the latest record
Reply with quote

yes i got the way to find the max , but for the first 8 chars do we need to use the SUBSTR for sql, but in COBOL pgm what we need to give?

The first 8 character should be same and maximum of 9th and 10th character
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon May 10, 2010 8:22 pm    Post subject:
Reply with quote

Hello,

How many different "first 8 character" values will be processed in one query?

If there will be more than one, you should show a better set of sample data. . .
Back to top
View user's profile Send private message
chrakesh

New User


Joined: 03 May 2010
Posts: 16
Location: india

PostPosted: Mon May 10, 2010 8:42 pm    Post subject:
Reply with quote

yes there will be more than one , so just we need to compare the data that is given say if there is '12345678' or '87984743' like that many number of part names, based on that we need to check the condition and check the max. of the remaining other two alphabets .
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Mon May 10, 2010 9:08 pm    Post subject: Reply to: How to find the latest record
Reply with quote

if the issue is related to a db2 query why ...
Quote:
but in COBOL pgm what we need to give?


how is the <thing> defined in the db2 table ?
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1218
Location: Israel

PostPosted: Mon May 10, 2010 10:22 pm    Post subject:
Reply with quote

I think this is what you mean:
Code:
SELECT * FROM your.table
   WHERE NIK = (SELECT MAX(NIK) FROM your.table WHERE NIK LIKE '12345678%')
NB: query not verified. Please double-check.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Mon May 10, 2010 10:33 pm    Post subject: Reply to: How to find the latest record
Reply with quote

I MIGHT BE WRONG, just asking
can the group by and the max be applied together with the substr ??
something along the lines of
Code:
max(substr(nik,9,2)) group by substr(nik,1,8)

approximate syntax icon_redface.gif
Back to top
View user's profile Send private message
chrakesh

New User


Joined: 03 May 2010
Posts: 16
Location: india

PostPosted: Tue May 11, 2010 10:07 am    Post subject:
Reply with quote

Thanks guys . Iwill check it out....
Back to top
View user's profile Send private message
chrakesh

New User


Joined: 03 May 2010
Posts: 16
Location: india

PostPosted: Tue May 11, 2010 10:11 am    Post subject:
Reply with quote

Marso wrote:
I think this is what you mean:
Code:
SELECT * FROM your.table
   WHERE NIK = (SELECT MAX(NIK) FROM your.table WHERE NIK LIKE '12345678%')
NB: query not verified. Please double-check.


If there many diff NIK id's , what we need to give?
Back to top
View user's profile Send private message
Binop B

Active User


Joined: 18 Jun 2009
Posts: 407
Location: Nashville, TN

PostPosted: Tue May 11, 2010 11:39 am    Post subject:
Reply with quote

Hi chrakesh,
Quote:
If there many diff NIK id's , what we need to give?
It would be better if you can explain your requirements better ... probably with some some samples and the desired output ... with the sample that you have given now there is only one NIK id ... Probably what you are looking for is the IN predicate ... icon_smile.gif
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue May 11, 2010 11:51 am    Post subject: Re: Reply to: How to find the latest record
Reply with quote

enrico-sorichetti wrote:
Code:
max(substr(nik,9,2)) group by substr(nik,1,8)

looks fine
you could just say (= same thing)
Code:
max(nik) group by substr(nik,1,8)


marso wrote:
Code:

SELECT * FROM your.table
   WHERE NIK = (SELECT MAX(NIK) FROM your.table WHERE NIK LIKE '12345678%')

chrakesh wrote:
If there many diff NIK id's , what we need to give?

My first answer is : your resignation, because anyone with basic SQL knowledge should be able to solve that himself. (=correlated subquery)
My second answer:
Code:
SELECT * FROM your.table A
   WHERE NIK = (SELECT MAX(B.NIK) FROM your.table B WHERE B.NIK between substr(a.NIK,1,8) and substr(a.NIK,1,8) !! x'FF')

you can just compare substr(), but between is indexable.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to find the first monday of the w... abdulrafi COBOL Programming 10 Fri Nov 25, 2016 3:24 pm
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts Find out a active PGM jpsager JCL & VSAM 10 Fri Oct 07, 2016 4:32 pm
No new posts sort with previous record anatol DFSORT/ICETOOL 9 Thu Oct 06, 2016 2:36 am
No new posts How to find a CICS resource used in C... Arunkumar Chandrasekaran CICS 8 Thu Sep 29, 2016 1:45 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us