Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 695
  • Last Modified:

Selecting and comparing row pairs in oracle sql

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
Iver Erling Arva
Asked:
Iver Erling Arva
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
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
 
Iver Erling ArvaSenior consultantAuthor Commented:
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
 
Geert GOracle dbaCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now