?
Solved

Get max date or null date - ORACLE 11g

Posted on 2012-08-22
3
Medium Priority
?
987 Views
Last Modified: 2012-08-22
How would I construct a SQL to get the this result
RESULT:

 TASK1    IN_PROGRESS
 TASK2    ASSIGN
 TASK3    COMPLETE

INPUT:
P_ID    TITLE    COMPLETE_DATE    STATUS
1         TASK1    01/01/2011             COMPLETED
1         TASK1                                   IN_PROGRESS

P1_ID   TITLE    COMPLETE_DATE   STATUS
1          TASK2                             ASSIGN


PI_ID   TITLE    COMPLETE_DATE    STATUS
 1      TASK3     01/01/2012             CLOSE-OUT
 1      TASK3     07/23/2012             COMPLETED
0
Comment
Question by:cookiejar
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 38321458
this should do:
 select title, status
  from ( select title, status, row_number() over (partition by p1_id order by nvl(complet_date, sysdate) desc) rn
   from your_input_table
  ) sq
where sq.rn = 1

Open in new window

explained on this article:
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38321514
If the post above with nvl(complete_date, sysdate) does not answer the question:

I'm not understanding the inputs/outputs.

I see what looks like three sets of inputs and no expected results.

Please post expected results.
0
 

Author Comment

by:cookiejar
ID: 38321577
The results were posted:

RESULT:

 TASK1    IN_PROGRESS
 TASK2    ASSIGN
 TASK3    COMPLETE


AngelIII's solution  works well.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

830 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