| how to query max(date) plus tuning [message #294654] |
Fri, 18 January 2008 06:02  |
white_phoenix
Messages: 19
Registered: November 2007
|
Junior Member |
|
|
Hi,
I have 2 tables let say emp_details, and log_table
emp_details
----------
stfid
status
reason
log_table
---------
stfid
status
reason
changedby
datechanged DATE;
Each time a record is updated in emp details , a record is written to log_table. What I am trying to do is display log_table latest changes. The emp_details can be updated as many times per day, but for each stfid only one record should be displayed, and only records that were updated that day should be displayed. How should I do this?
I want to create report from this query, so any trigger before report will do
p/s which one is faster? sub-query or joining tables?
[Updated on: Fri, 18 January 2008 06:03] Report message to a moderator |
|
|
|
|
|
|
|
| Re: how to query max(date) plus tuning [message #294747 is a reply to message #294659] |
Fri, 18 January 2008 21:34  |
white_phoenix
Messages: 19
Registered: November 2007
|
Junior Member |
|
|
ok, i've done this
select a.deptcd||'/'|| a.yearcd||'/'||a.monthcd||'/'||a.num||'/'||a.seqno ACCNO, a.changedt, a.reasons,a.userid,a.adjcd
from log_table a,
(select max(changedt) as latest,deptcd, yearcd,monthcd,num,seqno, adjcd
from log_table
where deptcd >= :v_frombr and deptcd <= :v_tobr
and changedt = :rep_date
and adjcd = 'V'
group by deptcd, yearcd, monthcd, num, seqno, adjcd)q1
where a.changedt = q1.latest
and a.deptcd = q1.deptcd and a.yearcd = q1.yearcd and a.monthcd=q1.monthcd and a.num=q1.num and a.seqno=q1.seqno
when I do sub-query q1, I'll get 2 rows of data (which is the result should be)
but, when I execute the whole sql it's giving 3 rows of data.
Because in one day, the table may be updated many times, but I only want take the latest changes per account.
The column change date store data as date, not datetime (with the hour, minute)
What do I do wrong??
|
|
|
|
| Re: how to query max(date) plus tuning [message #294787 is a reply to message #294747] |
Sat, 19 January 2008 03:31 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
| Quote: | The column change date store data as date, not datetime (with the hour, minute)
What do I do wrong??
|
It is not common to answer before putting question, but you did it precisely.
The rows in the table are NOT ordered unless you introduce column which stores the order. As the CHANGEDT contains only day, all rows in one day are supposed to be from the same moment. You may pick one (random) row using ROW_NUMBER analytics or ROWID pseudocolumn, but it may not be the latest one. Exactly how will you check whether correct row was given ONLY from LOG_TABLE content?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
