Solved

Selecting and comparing row pairs in oracle sql

Posted on 2013-12-16
4
592 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 77

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 77

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

695 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