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

How to find the latest record


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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

Superior Member


Joined: 14 Mar 2007
Posts: 10886
Location: italy

PostPosted: Mon May 10, 2010 5:11 pm
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
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
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
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

Moderator Emeritus


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

PostPosted: Mon May 10, 2010 8:22 pm
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
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

Superior Member


Joined: 14 Mar 2007
Posts: 10886
Location: italy

PostPosted: Mon May 10, 2010 9:08 pm
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: 1353
Location: Israel

PostPosted: Mon May 10, 2010 10:22 pm
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

Superior Member


Joined: 14 Mar 2007
Posts: 10886
Location: italy

PostPosted: Mon May 10, 2010 10:33 pm
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
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
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
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: 1281
Location: Belgium

PostPosted: Tue May 11, 2010 11:51 am
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Copy only TEXT or String from a record SYNCSORT 4
No new posts combine multiple unique records into ... DFSORT/ICETOOL 2
No new posts SORT on detail record, then repeat he... DFSORT/ICETOOL 3
No new posts how to complete the end of a record w... DFSORT/ICETOOL 2
No new posts Sort based on the record type DFSORT/ICETOOL 1
Search our Forums:

Back to Top