Select Only the Most Recent Row

Question

I'm using a query to pull the valuation cost of projects. It works well, except sometimes it returns multiple rows for a valuation.

How can I modify my query to get only the most recent record to avoid multiple rows?

Query

SELECT
    to_char( BVALUATN.G3_CALC_VALUE, '$999,999,999,999,999' ) AS "Valuation",
    TO_CHAR( G6ACTION.G6_ACT_DD, 'mm/dd/yyyy') AS "Final Date"

FROM PERMIT

LEFT OUTER JOIN G6ACTION G6ACTION
    ON  (PERMIT.B1_PER_ID1 = G6ACTION.B1_PER_ID1)
    AND (PERMIT.B1_PER_ID3 = G6ACTION.B1_PER_ID3)

LEFT OUTER JOIN BVALUATN BVALUATN ON (PERMIT.B1_PER_ID1 = BVALUATN.B1_PER_ID1 AND PERMIT.B1_PER_ID3 = BVALUATN.B1_PER_ID3)

WHERE TRUNC (G6ACTION.G6_ACT_DD) > TO_DATE ('01/01/2020', 'mm/dd/yyyy')
AND TRUNC (G6ACTION.G6_ACT_DD) < TO_DATE ('04/06/2022', 'mm/dd/yyyy')

ORDER BY "Valuation" DESC;

Output

Desired Output

I'm interested in seeing various suggestions to see what works best for different use-cases, e.g., using an aggregate function, subquery, etc. Thanks for any help.

I am using Oracle Database version 12.1.0.2.0.

Answer 1

You can do a group by on the valuation and select the max date like below

SELECT
    to_char( BVALUATN.G3_CALC_VALUE, '$999,999,999,999,999' ) AS "Valuation",
    MAX(TO_CHAR( G6ACTION.G6_ACT_DD, 'mm/dd/yyyy')) AS "Final Date"

FROM B1PERMIT

LEFT OUTER JOIN G6ACTION G6ACTION
    ON  (PERMIT.B1_PER_ID1 = G6ACTION.B1_PER_ID1)
    AND (PERMIT.B1_PER_ID3 = G6ACTION.B1_PER_ID3)

LEFT OUTER JOIN BVALUATN BVALUATN ON (PERMIT.B1_PER_ID1 = BVALUATN.B1_PER_ID1 AND PERMIT.B1_PER_ID3 = BVALUATN.B1_PER_ID3)

WHERE TRUNC (G6ACTION.G6_ACT_DD) > TO_DATE ('01/01/2020', 'mm/dd/yyyy')
AND TRUNC (G6ACTION.G6_ACT_DD) < TO_DATE ('04/06/2022', 'mm/dd/yyyy')
GROUP BY to_char( BVALUATN.G3_CALC_VALUE, '$999,999,999,999,999' )
ORDER BY "Valuation" DESC