Solved

Selecting and comparing row pairs in oracle sql

Posted on 2013-12-16
4
545 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 37

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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.

920 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

14 Experts available now in Live!

Get 1:1 Help Now