CODEWITHSUNDEEP
sqloracle-databaseselectgreatest-n-per-group
Copra Loom
Copra Loom

Reputation: 47

How do I say MAX date on join table

I am getting duplicate records on the join and I only want the MAX(most current) date on the NSANOTES.NOTEDATE. How do you do that please?

SELECT NSAACTIVITY.NSAID ,
NSAACTIVITY.NSASERVICEIMPACTED "Type",  
NSAACTIVITY.NSAAPPROVED,
NSAACTIVITY.NSADESCRIPTION,
NSANOTES.NSANOTE 
FROM NSAUSER.NSAACTIVITY INNER JOIN NSAUSER.NSANOTES ON NSAACTIVITY.NSAID=NSANOTES.NSAID
WHERE TO_CHAR(NSAACTIVITY.NSASTART, 'yyyy-mm-dd hh24:mi') BETWEEN '2013-06-28 10:59:59' AND '2013-07-01 06:00:01'

Upvotes: 0

Views: 137

Answers (3)

user2275460
user2275460

Reputation: 339

I think you are looking for something on lines of below. I took the clue about using timestamp instead of to_char on your WHERE clause. Rest is merely a use of RANK() function. Hope this helps

SELECT *
  FROM (SELECT act.NSAID,
               act.NSASERVICEIMPACTED "Type",
               act.NSAAPPROVED,
               act.NSADESCRIPTION,
               notes.NSANOTE,
               rank()
                  OVER (PARTITION BY act.NSAID ORDER BY notes.NOTEDATE DESC) rn
          FROM    NSAUSER.NSAACTIVITY act
               INNER JOIN
                  NSAUSER.NSANOTES notes
               ON act.NSAID = notes.NSAID
         WHERE act.NSASTART BETWEEN TIMESTAMP '2013-06-28 10:59:59' AND TIMESTAMP '2013-07-01 06:00:01'
                                                            )
                                                            where rn=1;

Upvotes: 2

Jafar Kofahi
Jafar Kofahi

Reputation: 763

I think something like this would do the trick (use a subquery)

SELECT NSAACTIVITY.NSAID ,
NSAACTIVITY.NSASERVICEIMPACTED "Type",  
NSAACTIVITY.NSAAPPROVED,
NSAACTIVITY.NSADESCRIPTION,
ns.NSANOTE 
FROM NSAUSER.NSAACTIVITY INNER JOIN NSAUSER.NSANOTES ns ON NSAACTIVITY.NSAID=ns.NSAID
WHERE TO_CHAR(NSAACTIVITY.NSASTART, 'yyyy-mm-dd hh24:mi') BETWEEN '2013-06-28  10:59:59' AND '2013-07-01 06:00:01'
and ns.NOTESDATE= (SELECT MAX(ns2.NOTESDATE) FROM NSAUSER.NSANOTES ns2 where ns2.NSAID = ns.NSAID)

Upvotes: 0

Ian Kenney
Ian Kenney

Reputation: 6446

Did you try using group by ? something like:

SELECT NSAACTIVITY.NSAID ,
NSAACTIVITY.NSASERVICEIMPACTED "Type",  
NSAACTIVITY.NSAAPPROVED,
NSAACTIVITY.NSADESCRIPTION,
NSANOTES.NSANOTE,
MAX(NSANOTES.NOTEDATE)
FROM NSAUSER.NSAACTIVITY INNER JOIN NSAUSER.NSANOTES ON NSAACTIVITY.NSAID=NSANOTES.NSAID
WHERE TO_CHAR(NSAACTIVITY.NSASTART, 'yyyy-mm-dd hh24:mi') BETWEEN '2013-06-28 10:59:59' AND '2013-07-01 06:00:01'
GROUP BY NSAACTIVITY.NSAID ,
NSAACTIVITY.NSASERVICEIMPACTED,  
NSAACTIVITY.NSAAPPROVED,
NSAACTIVITY.NSADESCRIPTION,
NSANOTES.NSANOTE

Upvotes: 1

Related Questions