SQL command to find the last record on an audit table Crystal report Oracle 10g

Hello,

I am writing a SQL command report on an Oracle 10g database.  The table tracks audits of changes to material's management inventory item records.  The table has almost two million records.  I would like your input on how to write an efficient query to find the last record updated for an item.
jrbledsoe001Asked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
The row_number() function is a data warehouse windowing function.  These types of functions are pretty powerful.  I suggest you spend a little time in the docs.

They can probably explain them better than I can here:

http://download.oracle.com/docs/cd/E11882_01/server.112/e16579/analysis.htm
0
 
slightwv (䄆 Netminder) Commented:
can crystal use the data warehouse functions?

select column from (
select column, row_number() over(partition by item order by some_date desc) myRowNum
)
where myRowNum=1
0
 
jrbledsoe001Author Commented:
Here is my SQL statement:
select
iau.item_group,    
iau.company,        
iau.location,      
iau.item,          
iau.r_date,        
iau.r_time,        
iau.fld_nbr,        
iau.before_image,
iau.after_image,
iau.operator_id,        
idi.fld_name
from lsfmigd.icaudit iau, lsfmigd.icdict idi
where iau.fld_nbr = idi.fld_nbr and
iau.item_group = 'HOSP' and
iau.company >= 0 and
iau.location = '     ' and      
iau.item='{?Item}';  

notes
 '{?Item}' is a SQL command parameter
I used the 'explain plan for' Oracle feature to create a query which uses the most efficient index columns
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
slightwv (䄆 Netminder) Commented:
Just add the row_number() call to that select, then wrap an outer select around that with the where clause I provided.

I can't test it but something like below.  I guessed at ordering by r_date.
select
item_group,     
company,        
location,       
item,           
r_date,         
r_time,         
fld_nbr,        
before_image,
after_image,
operator_id,        
fld_name
from 
(
select
iau.item_group,     
iau.company,        
iau.location,       
iau.item,           
iau.r_date,         
iau.r_time,         
iau.fld_nbr,        
iau.before_image,
iau.after_image,
iau.operator_id,        
idi.fld_name,
row_number() over(partition by iau.item order by iau.r_date desc) myRowNum
from lsfmigd.icaudit iau, lsfmigd.icdict idi
where iau.fld_nbr = idi.fld_nbr and
iau.item_group = 'HOSP' and
iau.company >= 0 and 
iau.location = '     ' and       
iau.item='{?Item}'
)
where myrownum=1;

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
I also suppose you could just use the MAX call and group by all the other columns.
0
 
jrbledsoe001Author Commented:
Here is my output

iau-ITEMAUDIT-sql.pdf
0
 
slightwv (䄆 Netminder) Commented:
You can add the r_time to my order.  You also have the option of converting the date and time columns into an Oracle date data type and use that.

Look up the TO_DATE function if you are interested.
0
 
jrbledsoe001Author Commented:
I need help modifying the SQL so that the query locates only the last records updated by date and time.
0
 
slightwv (䄆 Netminder) Commented:
What about what I posted in http:#35706968?

just change:
row_number() over(partition by iau.item order by iau.r_date desc) myRowNum

to
row_number() over(partition by iau.item order by iau.r_date desc, iau.r_time desc) myRowNum
0
 
jrbledsoe001Author Commented:
Hi slightwv,

I have to research your solution to have a better understanding.  I'm new to SQL Plus and don't understand yet what your code is doing.  I'll try it and let you know how it goes.  Thanks for posting so quickly to my question.
0
 
jrbledsoe001Author Commented:
BRAVO GENIUS!!!  This works perfectly.  Thank you for your help
0
 
slightwv (䄆 Netminder) Commented:
No problem.  Glad it worked for you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.