Home » SQL & PL/SQL » SQL & PL/SQL » how to query max(date) plus tuning (8)
how to query max(date) plus tuning [message #294654] Fri, 18 January 2008 06:02 Go to next message
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 #294658 is a reply to message #294654] Fri, 18 January 2008 06:18 Go to previous messageGo to next message
techno
Messages: 44
Registered: October 2003
Member
select stfid,status
from log_table log
where datechanged = (select max(datechanged) 
                     from log_table
                     where stfid = log.stfid  )
Re: how to query max(date) plus tuning [message #294659 is a reply to message #294654] Fri, 18 January 2008 06:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This question is a classical one.
It has been asked many and many times.
Please search here, on AskTom, on Google, on any Oracle forum.

Regards
Michel
Re: how to query max(date) plus tuning [message #294747 is a reply to message #294659] Fri, 18 January 2008 21:34 Go to previous messageGo to next message
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 Go to previous message
flyboy
Messages: 1832
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?
Previous Topic: counting
Next Topic: Language problem
Goto Forum:
  


Current Time: Sat Dec 03 00:56:10 CST 2016

Total time taken to generate the page: 0.08393 seconds