SAP (Documents/Screen shots/Complete Solution): RE:[peopletools-l] How to Find the Second Highest Max effdt from Job Table in PeopleSoft?

Reply from Mayank Jain on Apr 17 at 12:10 AM

Hi,

Create table like below one

CREATE TABLE EMPLOYEE( EMPLID NUMBER,
EFFDT DATE)

Insert data into table

insert into EMPLOYEE
values(99, to_date('1998/03/31:12:01:00AM', 'yyyy/mm/dd:hh:mi:ssam'));

SELECT * FROM EMPLOYEE

SELECT * FROM EMPLOYEE E WHERE 2= ( SELECT COUNT(DISTINCT E1.EFFDT) FROM EMPLOYEE E1
WHERE E1.emplid = E.emplid AND E1.effdt >= E.effdt
AND E1.EFFDT < = SYSDATE
)

the above one give the same result if in SQL you use getdate instead of sysdate.

if you want 3rd you can replace 2 from 3.

Regards
Mayank

---------------Original Message---------------
From: venki k
Sent: Monday, April 09, 2012 11:35 PM
Subject: How to Find the Second Highest Max effdt from Job Table in PeopleSoft?

How to find the second highest max effdt from job table in PeopleSoft?
Please respond!

Thanks,
venki

Reply to this email to post your response.

__.____._

Manage Settings | Unsubscribe | Create FAQ | Send Feedback