|
View previous topic :: View next topic
|
| Author |
Message |
sampaul4u
New User
Joined: 27 Mar 2010
Posts: 19
Location: folirida
|
|
|
|
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 |
|
 |
santoshkumarmanilakkoju
New User
.jpg)
Joined: 24 Nov 2009
Posts: 37
Location: Don't know
|
|
|
|
This can be a help;
SELECT EMPPLCY,PLCYINCEPDT FROM TABLE
WHERE PLCYINCEPDT = (SELECT MAX(PLCYINCEPDT)
FROM TABLE WHERE EMPPLCY LIKE '789AB%'); |
|
| Back to top |
|
|
sampaul4u
New User
Joined: 27 Mar 2010
Posts: 19
Location: folirida
|
|
|
|
Thanks Santhosh! It works... 
Thanks,
Paul. |
|
| Back to top |
|
|
santoshkumarmanilakkoju
New User
Joined: 24 Nov 2009
Posts: 37
Location: Don't know
|
|
|
|
Good to know it worked  |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 
d |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Quote: |
| And this is on the rise |
more like Standard Operating Procedure |
|
| Back to top |
|
|
|
|
