Selecting the latest record within a table

Question

I have ant an Oracle v11 database, and whilst I do not have the schema definition of the tables, I have illustrated what I am trying to achieve below.

This is what the table looks like

I am trying to transform the data by selecting only the latest rows, the table keeps an history of changes, I am not interested in the changes only the latest value for every present issue

This is what I have so far.

select issueno,
  case (when fieldname = 'name' then string_value end) name,
  case (when fieldname = 'point' then string_value end) point
from issues
where issueno = 1234

The issue with the query above is that it returns 4 rows, I would like to return only a single row.

Answer 1

You can get the latest date by using LAST ORDER BY clause within the MAX() KEEP (..) values for transition_date(or load_date column, depending on which you mean replace within the query) such as

WITH i AS
(
SELECT CASE WHEN fieldname = 'name' THEN
             MAX(string_value) KEEP (DENSE_RANK LAST ORDER BY transition_date)
                               OVER (PARTITION BY issue_no, fieldname) 
             END AS name,
       CASE WHEN fieldname = 'point' THEN
             MAX(string_value) KEEP (DENSE_RANK LAST ORDER BY transition_date)
                               OVER (PARTITION BY issue_no, fieldname) 
             END AS point                
  FROM issues 
)
SELECT MAX(name) AS name, MAX(point) AS point 
  FROM i

But, if ties(equal values) occur for the related date values, then consider using DENSE_RANK() function in order to compute the values returning equal to 1 along with ROW_NUMBER() to be able to use with the JOIN clause in the main query such as

WITH i AS
(
SELECT i.*,
       DENSE_RANK() OVER ( PARTITION BY issue_no, fieldname 
                               ORDER BY transition_date DESC) AS dr,
       ROW_NUMBER() OVER ( PARTITION BY issue_no, fieldname 
                               ORDER BY transition_date DESC) AS rn
  FROM issues i
)
SELECT i1.string_value AS name, i2.string_value AS point
  FROM ( SELECT string_value, rn FROM i WHERE dr = 1 AND fieldname = 'name'  ) i1
  FULL JOIN ( SELECT string_value, rn FROM i WHERE dr = 1 AND fieldname = 'point' ) i2
    ON i2.rn = i1.rn

Demo