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
 

 

Need a query to find MAX value

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

New User


Joined: 27 Mar 2010
Posts: 19
Location: folirida

PostPosted: Fri Jul 16, 2010 10:37 am    Post subject: Need a query to find MAX value
Reply with quote

Hi,

I have a table with the following 2 entries.

EMPPLCY PLCYINCEPDT
789AB09 2009-01-01
789AB10 2010-01-01


With the input of first 5 characters of EMPPLCY, how to find the EMPPLCY for the recent PLCYINCEPDT?

for the given example, my expected output is 789AB10 & 2010-01-01.

Please help me to sort it out.

Thanks,
Paul.
Back to top
View user's profile Send private message

santoshkumarmanilakkoju

New User


Joined: 24 Nov 2009
Posts: 37
Location: Don't know

PostPosted: Fri Jul 16, 2010 11:03 am    Post subject:
Reply with quote

This can be a help;

SELECT EMPPLCY,PLCYINCEPDT FROM TABLE
WHERE PLCYINCEPDT = (SELECT MAX(PLCYINCEPDT)
FROM TABLE WHERE EMPPLCY LIKE '789AB%');
Back to top
View user's profile Send private message
sampaul4u

New User


Joined: 27 Mar 2010
Posts: 19
Location: folirida

PostPosted: Fri Jul 16, 2010 11:27 am    Post subject: Reply to: Need a query to find MAX value
Reply with quote

Thanks Santhosh! It works... icon_smile.gif

Thanks,
Paul.
Back to top
View user's profile Send private message
santoshkumarmanilakkoju

New User


Joined: 24 Nov 2009
Posts: 37
Location: Don't know

PostPosted: Fri Jul 16, 2010 11:38 am    Post subject:
Reply with quote

Good to know it worked icon_biggrin.gif
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Jul 19, 2010 1:30 pm    Post subject:
Reply with quote

santoshkumarmanilakkoju wrote:
This can be a help;

SELECT EMPPLCY,PLCYINCEPDT FROM TABLE
WHERE PLCYINCEPDT = (SELECT MAX(PLCYINCEPDT)
FROM TABLE WHERE EMPPLCY LIKE '789AB%');


I hope there is only 1 row in all the table where plcyincepdt = '2010-01-01'
otherwise this fails miserably. the normal construct is as follows :

Code:
SELECT EMPPLCY,PLCYINCEPDT FROM TABLE1 A
WHERE a.EMPPLCY LIKE '789AB%'
  AND a.PLCYINCEPDT = (SELECT MAX(b.PLCYINCEPDT)
FROM TABLE1 b WHERE b.EMPPLCY = a.empplcy);
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 Jul 19, 2010 8:02 pm    Post subject: Reply to: Need a query to find MAX value
Reply with quote

Thanks Guy,

Just imagine how many things are put into productinon without proper testing because someone asked for or found the code on the internet. . .

Really scary. . .

And this is on the rise icon_neutral.gif

d
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Mon Jul 19, 2010 8:12 pm    Post subject:
Reply with quote

Quote:
And this is on the rise


more like Standard Operating Procedure
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 find RACF group for access to spooled... jzhardy JCL & VSAM 1 Mon May 08, 2017 11:46 am
No new posts To find out size allocated to a seque... ashek15 JCL & VSAM 15 Thu Apr 27, 2017 9:42 am
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am


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