By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,704 Members | 1,076 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,704 IT Pros & Developers. It's quick & easy.

Selecting Max Daterecord from Many Records

didajosh
P: 47
T1 is
FUND-CODE++++FUND-DESC++++ITEM-NO++++DOC-CODE
001------------------- AAA ------------------1-----------------56
001-------------------AAA-------------------3------------------54
002-------------------BBB-------------------6------------------77
002-------------------BBB-------------------7------------------45

T2 is
FUND-CODE++++ PROG-CODE++++ TRAN-NO++++ AMT
001---------------------------1001------------------------25-----------546
001-------------------------- 1001----------------------- 26-----------656
002-------------------------- 1002----------------------- 45-----------656
002---------------------------1002----------------------- 46-----------325

T3 is
PROG-CODE++++PROG-DESC ++++NAME++++ DATE
1001-------------------------ABC---------------------DJ-------06-12-2008
1001-------------------------ABC---------------------PK------06-15-2008
1002-------------------------XYZ----------------------JG-------05-06-2007
1002-------------------------XYZ----------------------IM-------16-11-2007

Now I want a resulting table, which is T1 with two more fields:

FUND-CODE+++FUND-DESC+++ ITEM-NO+++ DOC-CODE+++ PROG-CODE+++ PROG-DESC
So basically in T1 I want the related Prog code anddesc of the Fund code.

Can this be done...
how..?

Please help :(

Regards,
Dipali
Aug 26 '08 #1
Share this Question
Share on Google+
8 Replies


didajosh
P: 47
I don't know how to edit question...so replying to it..!!
T1 is
FUND-CODE++++FUND-DESC++++ITEM-NO++++DOC-CODE
001------------------- AAA ------------------1-----------------56
001-------------------AAA-------------------3------------------54
002-------------------BBB-------------------6------------------77
002-------------------BBB-------------------7------------------45

T2 is
FUND-CODE++++ PROG-CODE++++ TRAN-NO++++DATE
001---------------------------1001------------------------25-----------06-23-2008
001-------------------------- 1001----------------------- 26-----------07-2-2008
002-------------------------- 1002----------------------- 45-----------02-13-2007
002---------------------------1002----------------------- 46-----------04-14-2006

T3 is
PROG-CODE++++PROG-DESC ++++NAME
1001-------------------------ABC---------------------DJ
1001-------------------------ABC---------------------PK
1002-------------------------XYZ----------------------JG
1002-------------------------XYZ----------------------IM

Now I want a resulting table, which is T1 with two more fields:

FUND-CODE+++FUND-DESC+++ ITEM-NO+++ DOC-CODE+++ PROG-CODE+++ PROG-DESC
here PROG-CODE should come that of the LAST DATE...ie MAX date.
So basically in T1 I want the related Prog code and desc of the Prog code.

Can this be done...
how..?

Please help :(

Regards,
Dipali
Aug 26 '08 #2

amitpatel66
Expert 100+
P: 2,367
Please post what you have tried so far to achieve the result?
Aug 27 '08 #3

didajosh
P: 47
what I have tried is something like,

select T1.FUND-CODE,T1.FUND-DESC,T1.ITEM-NO,T1.DOC-CODE,T2.PROG-CODE,T3.PROG-DESC
FROM T1,T2,T3
WHERE
T1.FUND-CODE=T2.FUND-CODE
AND
T2.PROG-CODE = T3.PROG-CODE

now how do I link
T1.FUND-CODE=T2.FUND-CODE with the comdition to select Max date, group by T2.FUND-CODE

-dipali
Aug 27 '08 #4

didajosh
P: 47
My T1 Table is
FUND-CODE--------PROG-CODE--------A-DATE
001-----------------------21----------------------06-27-2003
001-----------------------21----------------------08-30-2004
001-----------------------15----------------------06-27-2003
002-----------------------71----------------------06-27-2003
002-----------------------14----------------------07-02-2002

I want Fund-code and Prog code with max date.
I use:
SELECT fund-code,prog-code,a-date from T1
where
a-date in
(
select max (a-date)
from T1
group by fund-code)

I get the result:
FUND-CODE--------PROG-CODE--------A-DATE
001-----------------------21----------------------06-27-2003
001-----------------------21----------------------08-30-2004
001-----------------------15----------------------06-27-2003
002-----------------------71----------------------06-27-2003

why????

please help... :(

Thanks & Regards,
-Dipali
Aug 27 '08 #5

10K+
P: 13,264
Remove the group by fund code part. Why did you put it there?
Aug 28 '08 #6

amitpatel66
Expert 100+
P: 2,367
what I have tried is something like,

select T1.FUND-CODE,T1.FUND-DESC,T1.ITEM-NO,T1.DOC-CODE,T2.PROG-CODE,T3.PROG-DESC
FROM T1,T2,T3
WHERE
T1.FUND-CODE=T2.FUND-CODE
AND
T2.PROG-CODE = T3.PROG-CODE

now how do I link
T1.FUND-CODE=T2.FUND-CODE with the comdition to select Max date, group by T2.FUND-CODE

-dipali
Expand|Select|Wrap|Line Numbers
  1.  
  2. select T1.FUND-CODE,T1.FUND-DESC,T1.ITEM-NO,T1.DOC-CODE,T2.PROG-CODE,T3.PROG-DESC
  3. FROM T1,T2,T3
  4. WHERE 
  5. T1.FUND-CODE=T2.FUND-CODE
  6. AND 
  7. T2.PROG-CODE = T3.PROG-CODE
  8. AND t2.date_column = (SELECT MAX(date_column) FROM T2 WHERE fund_code= t2.fund_code GROUP BY t2.fund_code)
  9.  
  10.  
Aug 28 '08 #7

amitpatel66
Expert 100+
P: 2,367
Duplicate threads merged for better management of the forum

MODERATOR
Aug 28 '08 #8

didajosh
P: 47
@Amit

Thank you for your response, but even that is not working.
I think the reason is, here users have manually edited the timestamps from back-end.
You think this can be a reason.

So, what I did was created temporary tables, and populate the data as Fund-Code ,Prog-Code and Prog-Code and Prog-Desc.

And then finally join tables.

What do you think of it?

Regards,
Dipali
Aug 29 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.