Solved

Selecting and comparing row pairs in oracle sql

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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 …
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…
Via a live example, show how to take different types of Oracle backups using RMAN.
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.

734 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