CODEWITHSUNDEEP
sqloracle-databasejoingreatest-n-per-group
artemis
artemis

Reputation: 7281

Oracle SQL - Select most recent occurrence of row

Here is my current situation: I have a series of subqueries that are building on one another. I have employees whose jobs are to write logs. These logs are then reviewed and edited, and I only want to pull the most recent edits (as each corresponding edit concatenates on top of the one before it.

So if I edit it at 10:00am, and then at 11:00am, the 11:00am edit will ALSO contain the text from the 10:00am, but both values are stored. It is just how our system stores information...can't change it). However, if I join the two tables together, it pulls EVERY edit for the given log.

My issue is that I have MULTIPLE logs, and want to pull only the most recent EDIT for each of the logs. I have tried the following which sort of worked, but it only pulled the OVERALL most recent edit, not the most recent for EACH log:

-- I have WITH and the other subqueries above...
sqEDITS AS
(
    SELECT
        Othertable.*, EDIT_TXT
    FROM
        Othertable
    LEFT JOIN 
        EDITS ON Othertable.LOG_NO = EDITS.EDIT_LOG_NO
    WHERE 
        EDIT_ACTIVITY_DT = (SELECT MAX(EDITS.EDIT_ACTIVITY_DT)
                            FROM Othertable, EDITS
                            WHERE Othertable.LOG_NO = EDITS.EDIT_LOG_NO) -- end where
) --end sqEDITS

In my test set, there are 35 unique logs, which should return 35 of the most recent edits for each one (if it exists).

My other issue is the TEXT data type, that I am trying to pull, is a CLOB, which makes it hard to work with.

Any advice would be greatly appreciated. I am surely stumped. Thanks!

Upvotes: 0

Views: 501

Answers (3)

ravioli
ravioli

Reputation: 3833

You can use a window function:

SELECT othertable.*, edit_txt
FROM othertable -- Get logs (?)
LEFT JOIN ( -- Get most recent edit for each log
    SELECT edit_txt
    FROM (
        SELECT edit_txt
        FROM edits
        WHERE RANK() OVER(PARTITION BY edit_log_no ORDER BY edit_activity_dt DESC) = 1 -- For each log_no, only return most recent edit (based on edit_activity_dt value)
    ) edits
) edits ON othertable.log_no = edits.edit_log_no

Let me know if that works.

Update
This should work:

SELECT othertable.*, edit_txt
FROM othertable -- Get logs (?)
LEFT JOIN ( -- Get most recent edit for each log
    SELECT edit_txt, edit_log_no
    FROM (
      SELECT edit_txt, edit_log_no, RANK() OVER(PARTITION BY edit_log_no ORDER BY edit_activity_dt DESC) EditRank
      FROM edits
    ) src
    WHERE EditRank = 1
) edits ON othertable.log_no = edits.edit_log_no

You could move the "WHERE EditRank = 1" to the JOIN condition and get rid of a sub-select. Not sure if this would improve performance, but readability would be easier.

Upvotes: 1

Aydın Özgür
Aydın Özgür

Reputation: 24

You should use analytic function for rank. 

SELECT columna,columnb FROM (
  SELECT columna,columnb
  RANK() OVER (PARTITION BY columna ORDER BY (column of date ) DESC) AS  RNK   
       FROM  table 
) WHERE RNK=1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270683

Never use commas in the FROM clause. Learn to use proper, explicit JOIN syntax.

In your case, the problem is that the subquery references two tables. I think you intend a correlated subquery:

sqEDITS AS (
         SELECT Othertable.*, EDIT_TXT
         FROM Othertable LEFT JOIN
              EDITS
              ON Othertable.LOG_NO = EDITS.EDIT_LOG_NO
        WHERE EDIT_ACTIVITY_DT = 
              (SELECT MAX(EDITS.EDIT_ACTIVITY_DT)
               FROM EDITS
               WHERE Othertable.LOG_NO = EDITS.EDIT_LOG_NO
              ) -- end where
       ) --end sqEDITS

Upvotes: 0

Related Questions