Home » RDBMS Server » Server Administration » Second Record for each employee
Second Record for each employee [message #373526] Fri, 20 April 2001 14:34 Go to next message
aish74
Messages: 19
Registered: March 2001
Junior Member
Hi ,
How can I get the second RECORD FOR EACH employee
For I have a transfer hist TABLES

Transfer_hist

(div/
emplid territory) transfer_dt completion_dt

10001 AAA 4/13/2001 Still working
10001 BBB 3/3/1999 2/02/2000
10001 CCC 1/1/1998 05/05/1999

20001 DDD 1/13/2001 03/03/2001
20001 EEE 6/3/1999 9/02/2000
20001 FFF 3/1/1998 05/05/1999

I want TO find the second RECORD for EACH employee .Please suggest IF possible can anybody WRITE a query for me.
I can USE the MAX transfer_dt TO get the latest RECORD OR first RECORD but how DO i get the second RECORD

Thanks,
Aish

Report message to a moderator

Re: Second Record for each employee [message #373549 is a reply to message #373526] Mon, 23 April 2001 09:40 Go to previous messageGo to next message
Balamurugan.R
Messages: 91
Registered: March 2001
Member
Hi,

Try this

select * from transfer_hist x where x.transfer_dt=(select transfer_dt from transfer_hist where transfer_dt<=(select max(transfer_dt) from transfer_hist where empid=x.empid) and transfer_dt>=(select min(transfer_dt) from transfer_hist where empid=x.empid) and empid=x.empid;

Hope this will help u.
Let me know it works well or not.

Cherrs,
Balu

Report message to a moderator

Re: Second Record for each employee [message #373552 is a reply to message #373526] Mon, 23 April 2001 10:38 Go to previous messageGo to next message
aish74@yahoo.com
Messages: 2
Registered: April 2001
Junior Member
Hi,

Bala it is giving me all the records.Not just the second record

Thanks,
Aish

Report message to a moderator

Re: Second Record for each employee [message #373553 is a reply to message #373526] Mon, 23 April 2001 10:38 Go to previous messageGo to next message
aish74@yahoo.com
Messages: 2
Registered: April 2001
Junior Member
Hi,

Balu it is giving me all the records.Not just the second record

Thanks,
Aish

Report message to a moderator

Re: Second Record for each employee [message #373555 is a reply to message #373526] Mon, 23 April 2001 10:52 Go to previous messageGo to next message
aish
Messages: 44
Registered: March 2001
Member
Hi Thirumalai,
Thanks for your help there is some thing missing i this query for the fact that sometime its giving me third record sometimes 2 nd and sometimes 4th record .COuld you please let me know how do I modify the query.

Thanks
Aish

Report message to a moderator

Re: Second Record for each employee [message #373590 is a reply to message #373526] Wed, 25 April 2001 02:37 Go to previous message
Balamurugan.R
Messages: 91
Registered: March 2001
Member
Hi,

sorry for that, please try this

select * from transfer_hist x where transfer_dt=
(select max(transfer_dt) from ex1 where empid=x.empid and transfer_dt=(select max(transfer_dt) from ex1 where empid=x.empid));

I hope this query will solve ur problem.

Let me know this will works according to your expectation. Thanks for your earlier reply to my query.

Cheers,
Balu.

Report message to a moderator

Previous Topic: Returned fields into variables
Next Topic: Re: Copy/select commands using Database Links
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Jul 02 17:32:03 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.