Topic: SQL*Plus and PL/SQL >> GETTING THE LATEST RECORD
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| Title: GETTING THE LATEST RECORD |
| Randy |
Posted: Jul 11, 2007 10:21:58 AM |
Total Post: 4
Joined: May, 2007
|
Hello Everyone,
I would like advise on what I am doing. I am trying to get the maximum date per record from a table. It is a very simple query but I am wondering why I am not getting the right result. Please look at the following query:
select account_id, amount, max(tran_date) from evalucheck_history group by account_id, check_amount order by account_id
This query is supposed to give me the record with the latest date but instead I get the following (snap shot of the result):
Account ID Amt Date
9999999999000100 174.8 1-Dec-2006
9999999999000100 223.69 25-Oct-2006
9999999999000100 358.5 9-Nov-2006
9999999999000100 393.5 14-Nov-2006
9999999999000100 441.98 24-Oct-2006
9999999999000100 476.93 20-Oct-2006
9999999999000100 552.07 10-Jan-2007
9999999999000100 627.23 2-Nov-2006
9999999999000100 705.94 19-Oct-2006
9999999999000100 713.61 4-Dec-2006
9999999999000100 729.71 30-Oct-2006
9999999999000100 747.24 13-Mar-2007
9999999999000100 998.97 19-Apr-2007
Can you please help me on this?
Thank you all.
Randy |
|
|
 KS |
| Posted: Jul 11, 2007 10:52:43 AM |  |
|
Total Post: 65
Joined: Nov, 2006
|
Hi ,
The problem in your query is the AMT is varying, so you query gives you the
maximum date for a particular AMOUNT ID and AMT and not for only the AMOUNTID
Try this one,
select a.account_id, a.amount, max(tran_date) as tran_date
from evalucheck_history a ,(select account_id,max(tran_date)
from evalucheck_history
group by account_id
order by account_id ) b
where a.account_id=b.account_id
and a.tran_date =b.tran_date;
Work Around :
I simulated a similar kind of table,
SQL> desc t2
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER
B NUMBER
T_DATE DATE
SQL> select * from t2;
A B T_DATE
---------- ---------- ---------
100 34 01-DEC-06
100 20 01-JAN-06
100 10 15-JAN-06
100 121 15-JAN-06
100 121 05-JAN-06
100 1 05-JUN-06
100 32 01-DEC-06
7 rows selected.
SQL> select t2.a,t2.b,t2.t_date
2 from t2 , (select a,max(t_date) t_date
3 from t2 group by a) t
4 where t.a=t2.a
5 and t.t_date=t2.t_date;
A B T_DATE
---------- ---------- ---------
100 32 01-DEC-06
100 34 01-DEC-06
Regards,
Sri.
|
|
|
|
|
| Randy |
| Posted: Jul 11, 2007 11:43:59 AM | |
|
Total Post: 4
Joined: May, 2007
|
Thank you very much Sri. This one worked. I will use it to create a more complex query. I will ask you again, if you don't mind, later should it not work.
Randy
|
|
|
|
|
| KS |
| Posted: Jul 11, 2007 11:13:47 PM | |
|
Total Post: 65
Joined: Nov, 2006
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 857 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Gopu Gopi | 335 |
| Gitesh Trivedi | 322 |
| Vinoth Kumar | 264 |
| neeraj sharma | 258 |
| Ramesh Jois | 246 |
|
|
