Solved

Selecting and comparing row pairs in oracle sql

Posted on 2013-12-16
4
538 Views
Last Modified: 2014-01-03
I have a table with thousands of records and many records per customer number. Each record also contains dates.
I need to find the two most recent records for each customer. How should I go about that using a select in oracle sql?

Ex.

Full tableshould end up asReturned dataetc.

Thx!
0
Comment
Question by:IverErling
  • 2
4 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39722042
Assuming the date column is actually a date try something like this.

I had to change the column names since I don't think you can have a column in a table in oracle called date.  Not sure about data but changed that just in case.

select rownum, customer, mydate, mydata from
(select customer, mydate, mydata, row_number() over(partition by customer order by customer, date desc) myrn from table)
where myrn <=2;

Open in new window

0
 

Author Comment

by:IverErling
ID: 39723346
Thx! But there is still a problem. Here is my actual code:
select CUST_NO "Customer number",
       NAME "Name",
       CIVIL_REG_CODE "Personal reg no",
       DISABILITY_PCT "DSP per centage",
       trunc(TIMESTAMP)
  from (select t.CUST_NO,n.NAME,n.Civil_Reg_Code,t.DISABILITY_PCT,t.TIMESTAMP, row_number() 
               over(partition by t.CUST_NO ORDER BY t.CUST_NO, t.TIMESTAMP DESC) myrn 
               from TIA.OBJ_IPGEN t, TIA.NAME n
               where myrn <= 2
               and n.ID_NO = t.CUST_NO
               and t.suc_seq_no IS NULL
               and t.DISABILITY_PCT <> 0)

Open in new window


I get an error message saying ORA-00904: "MYRN": invalid identifier.

iVer
0
 
LVL 36

Accepted Solution

by:
Geert Gruwez earned 500 total points
ID: 39724057
you can't use the alias in the subquery.
it needs to be used in the toplevel
select CUST_NO "Customer number",
       NAME "Name",
       CIVIL_REG_CODE "Personal reg no",
       DISABILITY_PCT "DSP per centage",
       trunc(TIMESTAMP)
  from (select t.CUST_NO,n.NAME,n.Civil_Reg_Code,t.DISABILITY_PCT,t.TIMESTAMP, row_number() 
               over(partition by t.CUST_NO ORDER BY t.CUST_NO, t.TIMESTAMP DESC) myrn 
               from TIA.OBJ_IPGEN t, TIA.NAME n
               where n.ID_NO = t.CUST_NO
               and t.suc_seq_no IS NULL
               and t.DISABILITY_PCT <> 0)
where myrn <= 2

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39753807
iVer,

Can I ask why there wasn't a point split since the accepted answer was built off a previous post and why the penalty grade of a "B"?

Please review:
http://support.experts-exchange.com/customer/portal/articles/481419-what-grade-should-i-award-?b_id=44

and:
http://support.experts-exchange.com/customer/portal/articles/608596-how-do-i-accept-multiple-comments-as-my-solution-?b_id=44
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

708 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

12 Experts available now in Live!

Get 1:1 Help Now