Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 6968
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 HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Command to find TMEMBER name for a TPIPE Siva NKK Kothamasu IMS DB/DC 0 Wed Aug 16, 2017 6:08 pm
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us