Solved

Last record chosen in Oracle Query

Posted on 2016-10-19
3
81 Views
Last Modified: 2016-10-20
I need to only retrieve the last record when this Oracle 9.2 query executes.

DI  2016-09-26  168398    063848979   //SRC RMS


I would also like another Oracle query that only retrieves the first record.

NULL      DI  2016-09-16  174261    063848979   //SRC RMS             FB BRAMPTON TO

Right now,  the query will retrieve 3 records for this problem when it executes.
The 1993237 will be a field once I get it to work.
This Oracle query executes inside a SQL query.




SELECT (CASE WHEN TO_CHAR(aa.NOTES)  IS NOT NULL  THEN  TO_CHAR(aa.NOTES)  ELSE TO_CHAR(a.PTEXT) END) as MyNote
FROM            GPCOMP1.GPPROB a, GPCOMP1.GPPROBNOTES aa
WHERE        a.PROBLEM_ID = aa.PROB_ID AND (a.PROBLEM_ID = 1993237)
ORDER BY a.MODIFIED_ON DESC




 Oracle Query
0
Comment
Question by:thayduck
[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
3 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 41850949
First row:
select mynote from (
SELECT (CASE WHEN TO_CHAR(aa.NOTES)  IS NOT NULL  THEN  TO_CHAR(aa.NOTES)  ELSE TO_CHAR(a.PTEXT) END) as MyNote,
row_number() over(order by modified_on asc) rn
 FROM            GPCOMP1.GPPROB a, GPCOMP1.GPPROBNOTES aa
 WHERE        a.PROBLEM_ID = aa.PROB_ID AND (a.PROBLEM_ID = 1993237)
)
where rn=1


Last row:
select mynote from (
SELECT (CASE WHEN TO_CHAR(aa.NOTES)  IS NOT NULL  THEN  TO_CHAR(aa.NOTES)  ELSE TO_CHAR(a.PTEXT) END) as MyNote,
row_number() over(order by modified_on desc) rn
 FROM            GPCOMP1.GPPROB a, GPCOMP1.GPPROBNOTES aa
 WHERE        a.PROBLEM_ID = aa.PROB_ID AND (a.PROBLEM_ID = 1993237)
)
where rn=1


I'm guessing that really isn't what you want.  Can you provide some sample data and exepected results?
0
 

Author Comment

by:thayduck
ID: 41852218
Your code did exactly what I wanted.

 I did have to change :

 row_number() over(order by modified_on desc) rn

 TO

 row_number() over(order by a.modified_on desc) rn


 Thank you for the quick response.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41852228
No problem.  Glad to help.
0

Featured Post

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

627 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