Solved

How to write query to select only the records with latest update from a table

Posted on 2011-09-13
14
450 Views
Last Modified: 2012-05-12
Hi experts,

I have to work with a table that stores records of orders of equipment.  Now, each equipment may have multiple orders that is marked with date.  What will be the query for me to run so that it produces a list of unique equipment name AND only the latest date stamp only.

Here is a sample of my table

Date      OrderType      OrderNbr      EQUIPMENT
8/2/2011      AD      FI32883789      PAFY002
7/19/2011      CS      FI33536842      PAFY002
8/1/2011      AD      FI33450184      PAGA005
7/6/2011      AD      FI32484236      PAGA005
8/26/2011      AD      FI30572838      PAGB082
6/11/2011      AD      FI30726862      PAGB082
7/29/2011      AD      FI32248605      PAGB083
7/19/2011      AD      FI32183928      PAGB083
7/11/2011      AD      FI32882597      PAGB108
6/11/2011      AD      FI30728099      PAGB108
8/26/2011      AD      FI30555851      PAGB109
7/7/2011      AD      FI30555884      PAGB109
8/26/2011      AD      FI30570443      PAGB110
6/1/2011      AD      FI32204764      PAGF051
7/1/2011      AD      FI32170845      PAGH064
6/18/2011      AD      FI30729708      PAGH064
6/18/2011      AD      FI30728336      PAGP099
8/5/2011      MD      FI33739904      PAGP105

I am looking to have the end result from the query like this

Date      OrderType      OrderNbr      EQUIPMENT
8/2/2011      AD      FI32883789      PAFY002
8/1/2011      AD      FI33450184      PAGA005
8/26/2011      AD      FI30572838      PAGB082
7/29/2011      AD      FI32248605      PAGB083
7/11/2011      AD      FI32882597      PAGB108
8/26/2011      AD      FI30555851      PAGB109
8/26/2011      AD      FI30570443      PAGB110
6/1/2011      AD      FI32204764      PAGF051
7/1/2011      AD      FI32170845      PAGH064
6/18/2011      AD      FI30728336      PAGP099
8/5/2011      MD      FI33739904      PAGP105

NOTE:
The table is in Oracle and I have TOAD to run query to it.  Thus would be nice to have Oracle query for this.

0
Comment
Question by:ns2newmind
  • 7
  • 5
  • 2
14 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
something like:

select Date_col, OrderType, OrderNbr, EQUIPMENT
from (
  select Date_col, OrderType, OrderNbr, EQUIPMENT, row_number() over(partition by equipment order by date desc) myrownum
) where myrownum=1;
0
 

Author Comment

by:ns2newmind
Comment Utility
slightwv:

Thanks for the info. As I am not the SQL expert, I hope the syntax is ready to be used on TOAD. my VPN access is down now, will try that in a moment. But looking at the query, shouldn't there be a FROM in the inner SELECT to point to the initial table name?

select Date_col, OrderType, OrderNbr, EQUIPMENT
from (
           select Date_col, OrderType, OrderNbr, EQUIPMENT,
                     row_number() over(partition by equipment order by date desc) myrownum)
where myrownum=1;
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Toad should run the syntax just fine.  Not a toad person but have not heard anyone here complain about the data warehouse window function calls causing any problems.

>>But looking at the query, shouldn't there be a FROM in the inner SELECT to point to the initial table name?

oops...  yes.  Sorry.

I just typed it in without actually running it:


select Date_col, OrderType, OrderNbr, EQUIPMENT
from (
  select Date_col, OrderType, OrderNbr, EQUIPMENT, row_number() over(partition by equipment order by date desc) myrownum
from table_name
) where myrownum=1;
 
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
OK, last post without trying it.

There was one other typo.  Below is a fully tested setup.

I made up the date column name since 'date' is a reserved word in Oracle.
drop table tab1 purge;
create table tab1(myDate date, OrderType char(2), OrderNbr varchar2(12), EQUIPMENT varchar2(10));


insert into tab1 values(to_date('8/2/2011','MM/DD/YYYY'),'AD','FI32883789','PAFY002');
insert into tab1 values(to_date('7/19/2011','MM/DD/YYYY'),'CS','FI33536842','PAFY002');
insert into tab1 values(to_date('8/1/2011','MM/DD/YYYY'),'AD','FI33450184','PAGA005');
insert into tab1 values(to_date('7/6/2011','MM/DD/YYYY'),'AD','FI32484236','PAGA005');
insert into tab1 values(to_date('8/26/2011','MM/DD/YYYY'),'AD','FI30572838','PAGB082');
insert into tab1 values(to_date('6/11/2011','MM/DD/YYYY'),'AD','FI30726862','PAGB082');
insert into tab1 values(to_date('7/29/2011','MM/DD/YYYY'),'AD','FI32248605','PAGB083');
insert into tab1 values(to_date('7/19/2011','MM/DD/YYYY'),'AD','FI32183928','PAGB083');
insert into tab1 values(to_date('7/11/2011','MM/DD/YYYY'),'AD','FI32882597','PAGB108');
insert into tab1 values(to_date('6/11/2011','MM/DD/YYYY'),'AD','FI30728099','PAGB108');
insert into tab1 values(to_date('8/26/2011','MM/DD/YYYY'),'AD','FI30555851','PAGB109');
insert into tab1 values(to_date('7/7/2011','MM/DD/YYYY'),'AD','FI30555884','PAGB109');
insert into tab1 values(to_date('8/26/2011','MM/DD/YYYY'),'AD','FI30570443','PAGB110');
insert into tab1 values(to_date('6/1/2011','MM/DD/YYYY'),'AD','FI32204764','PAGF051');
insert into tab1 values(to_date('7/1/2011','MM/DD/YYYY'),'AD','FI32170845','PAGH064');
insert into tab1 values(to_date('6/18/2011','MM/DD/YYYY'),'AD','FI30729708','PAGH064');
insert into tab1 values(to_date('6/18/2011','MM/DD/YYYY'),'AD','FI30728336','PAGP099');
insert into tab1 values(to_date('8/5/2011','MM/DD/YYYY'),'MD','FI33739904','PAGP105');
commit;


select mydate, OrderType, OrderNbr, EQUIPMENT
from (
  select mydate, OrderType, OrderNbr, EQUIPMENT, row_number() over(partition by equipment order by mydate desc) myrownum
from tab1
) where myrownum=1;

Open in new window

0
 

Author Comment

by:ns2newmind
Comment Utility
slightwv:

Thanks, looks like it is working.  Just one more tweaking question please.
Since my Table is quite large, if I want to filter just the order in 2011 say month 05, 06, 07 where should I place the TO_CHAR(mydate, 'YYYY') and TO_CHAR(mydate, 'MM')

I tried putting then either (a) as a WHERE right behind tab1 and (b) add AND right after myrownum=1
the query seems to run but return nothing
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>if I want to filter

Filter to me means adding to the where clause.

>>just the order in 2011 say month 05, 06, 07

To me this means sorting.

I'm not understanding what you are wanting to do.

0
 
LVL 16

Expert Comment

by:Swadhin Ray
Comment Utility
Try this:

select DATE_O , OrderType, OrderNbr, EQUIPMENT from (
select DATE_O , OrderType, OrderNbr, EQUIPMENT, RANK() OVER (PARTITION BY EQUIPMENT ORDER BY DATE_O ) rank
from  order_tab ) where rank=1;
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
slobaray,

In this case how is RANK different than ROW_NUMBER?
0
 

Author Comment

by:ns2newmind
Comment Utility
slightwv:

Your query works. Since the actual table has many more orders than the sample given (It has orders since 2001 to today and each month has approx 4,000 orders), I'd like to set the WHERE statement so I can have the result only for order in 2011 and as recent as only in May (05), June(06) and July (07).

So, I think I should do the following:

select mydate, OrderType, OrderNbr, EQUIPMENT
from   (
            select mydate, OrderType, OrderNbr, EQUIPMENT,
            row_number() over(partition by equipment order by mydate desc) myrownum
            from tab1
            )
where myrownum=1 AND TO_CHAR(mydate, 'YYYY' = '2011') AND
            TO_CHAR(mydate, 'MM') IN ('05', '06', '07') ;

But somehow when I run this query, it runs but return zero rows :-(
0
 

Author Comment

by:ns2newmind
Comment Utility
slightwv:

I tried it again with this one

select mydate, OrderType, OrderNbr, EQUIPMENT
from   (
            select mydate, OrderType, OrderNbr, EQUIPMENT,
                       row_number() over(partition by equipment order by mydate desc) myrownum
            from    tab1
            where TO_CHAR(mydate, 'YYYY' = '2011') AND TO_CHAR(mydate, 'MM') IN ('05', '06', '07')
            )
where myrownum=1 ;

and it works beautifully.  I think the earlier query should also works but perhaps I made some syntax error or something.

Anyway, THANK YOU VERY MUCH for your advice.  It helps me get this work done and I also learn something new (from a non-SQL expert stand point).

Cheers,
0
 

Author Closing Comment

by:ns2newmind
Comment Utility
Wow, excellent advice in a very short period of time from the #1 Guru (according to the HOF table).
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
The where needs to be on the inner query.

Your way:

select mydate, OrderType, OrderNbr, EQUIPMENT
from   (
            select mydate, OrderType, OrderNbr, EQUIPMENT,
            row_number() over(partition by equipment order by mydate desc) myrownum
            from tab1 where TO_CHAR(mydate, 'YYYY' = '2011') AND
            TO_CHAR(mydate, 'MM') IN ('05', '06', '07')
            )
where myrownum=1;



If you know your dates I would use something like:

select mydate, OrderType, OrderNbr, EQUIPMENT
from   (
            select mydate, OrderType, OrderNbr, EQUIPMENT,
            row_number() over(partition by equipment order by mydate desc) myrownum
            from tab1 where mydate between >= to_date('05/01/2011','MM/DD/YYYY') and mydate <  to_date('08/01/2011','MM/DD/YYYY')           )
where myrownum=1
0
 
LVL 16

Expert Comment

by:Swadhin Ray
Comment Utility
@slightwv : The time I posted I saw only the 1st post i.e on  ID:36530873 of yours .. it was a mistake that I didn't refreshed my page ... and when I posted it you already made the your query changes on your post i.e. ID:36531607.

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
ns2newmind,

glad to help.

>>The time I posted I saw only the 1st post i.e on  ID:36530873 of yours .. it was a mistake that I didn't refreshed my page

The correction was posted almost a full hour before your post.  Please try to refresh after an hour goes by from the time you pull up a question and actually post.

If you read a question you are interested in and want to work on I suggest you click the 'Monitor' link at the top of the question.  Then while you are working on it if someone posts, you will receive the email notification.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now