Add case when to row_number over partition clause - ORACLE 11g

I realize I've posted a similar question and the solution worked great.  However, there's another component that was added to input.

The solution was:  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

My question is how to I add to the row_number clause the following  
order by case dept = current department then 1 case select count(1) from input where dept = current = 0 then 1 else 2,nvl(complete_date, sysdate) desc)

See the input and results below:

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

P1_ID   DEPT                 TITLE    COMPLETE_DATE   STATUS
1          CURRENT           TASK2                                  ASSIGN


PI_ID   DEPT                   TITLE    COMPLETE_DATE      STATUS
 1        CURRENT             TASK3     01/01/2012             CLOSE-OUT
 1        CURRENT             TASK3     07/23/2012             COMPLETED


PI_ID   DEPT                   TITLE    COMPLETE_DATE      STATUS
 1        CURRENT             TASK4     01/01/2012             ACTIVE
 1        PREVIOUS            TASK4     07/23/2012             CHECKED_OUT

PI_ID   DEPT                   TITLE    COMPLETE_DATE      STATUS
 1        PREVIOUS            TASK5     01/01/2012             EXPIRED
 1        PREVIOUS            TASK5     07/23/2012             ACTIVE

RESULT:
TASKS1     IN_PROGRESS
TASK2       ASSIGN
TASK3      COMPLETED
TASK4       ACTIVE
TASK5       ACTIVE
cookiejarAsked:
Who is Participating?
 
sdstuberCommented:
sorry, I misunderstood,  so assuming your "current" department is in a variable called
v_dept

try the attached.

you still don't need to do a count or anything complicated like that,
but numbering them does work.
ee.txt
0
 
sdstuberCommented:
I'm not entirely sure what your case was supposed to say
but this produces the requested results.
I didn't put an order by on the final results, but if you need them sorted by title
simply add

order by title

to the end
ee.txt
0
 
sdstuberCommented:
I think I figured it out.  you're trying to sort the dept values so CURRENT comes before PREVIOUS by assigning each a number and you're checking to see there are more than one dept for each title to force current to 1 and previous to 2

No need for all that complication

ORDER BY dept ASC  is sufficient to do that for you.

If you only have current the order by does nothing
if you only have previous the order by does nothing
if you have both current and previous, current is ordered first

after evaluating that, the date is then used as in the original query
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
cookiejarAuthor Commented:
I am sorry, I used current and previous as pseudcode, shoudn't have been a value in the input.  


Personnel Record shows the current dept.

P_ID   DEPT        
  1      01034

Personnel Training Record
 PI_ID   DEPT                   TITLE     COMPLETE_DATE      STATUS
 1         01034                   TASK4     01/01/2012               ACTIVE
 1         02341                   TASK4     07/23/2012              CHECKED_OUT

PI_ID   DEPT                   TITLE    COMPLETE_DATE      STATUS
 1        02341                   TASK5     01/01/2012             EXPIRED
 1        02341                   TASK5     07/23/2012             ACTIVE

In example 1 for TASK4, there is  personnel current assigned department (01034) set that record to 1, 02341 to 2

In example 2  TASK5, no current assigned deparment exists, only the previous, set both to 1

I have the current department value stored in variable in the stored procedure

I would like to pass it into the row_number clause into the sql statement.  Maybe you have a better solution.

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
0
 
sdstuberCommented:
but,  given the data above, the original query should still work because

01034 < 02341

so it will still sort first and produce the expected results
0
 
cookiejarAuthor Commented:
but,  given the data above, the original query should still work because

01034 < 02341

so it will still sort first and produce the expected results

The department assignment will not always be numeric may be a combination of numbers and letters.

-----------------------------
CASE WHEN dept = v_dept THEN 1 ELSE 2 END
This will handle if there is a current and previous dept.
How would I handle if the records contain only the previous dept then rn =2?
As a result, no data will be returned  (WHERE sq.rn = 1)
0
 
sdstuberCommented:
>>> How would I handle if the records contain only the previous dept

the case will work just fine as is

if it doesn't, post sample data and expected results
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.

All Courses

From novice to tech pro — start learning today.