View previous topic :: View next topic
|
Author |
Message |
chrakesh
New User
Joined: 03 May 2010 Posts: 16 Location: india
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10888 Location: italy
|
|
|
|
MAX function ??? |
|
Back to top |
|
|
chrakesh
New User
Joined: 03 May 2010 Posts: 16 Location: india
|
|
|
|
yes,finding which is max. and the latest one... |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
Back to top |
|
|
chrakesh
New User
Joined: 03 May 2010 Posts: 16 Location: india
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
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 |
|
|
chrakesh
New User
Joined: 03 May 2010 Posts: 16 Location: india
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10888 Location: italy
|
|
|
|
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 |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006 Posts: 1353 Location: Israel
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10888 Location: italy
|
|
|
|
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 |
|
Back to top |
|
|
chrakesh
New User
Joined: 03 May 2010 Posts: 16 Location: india
|
|
|
|
Thanks guys . Iwill check it out.... |
|
Back to top |
|
|
chrakesh
New User
Joined: 03 May 2010 Posts: 16 Location: india
|
|
|
|
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 |
|
|
Binop B
Active User
Joined: 18 Jun 2009 Posts: 407 Location: Nashville, TN
|
|
|
|
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 ... |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
|