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.

It sounds like you want the last row per valuation, and not just the most recent row overall. Is that right? — William Robertson, Apr 06 '22 at 19:09

score 1 · Accepted Answer · answered Apr 06 '22 at 18:59

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

Barbaros Özhan · Answer 2 · 2022-04-06T19:16:33.037

0

As you're using DB version 12c+, then you can use FETCH clause following ORDER BY for ascending "Valuation" such as

ORDER BY "Valuation", "Final Date" DESC
FETCH FIRST 1 ROW ONLY

If you needed both of the equal values, then you'd replace ONLY with WITH TIES and remove the part , "Final Date" DESC

edited Apr 06 '22 at 19:16

answered Apr 06 '22 at 19:11

Barbaros Özhan

59,113
10
31
55

Looking for last row per valuation, not just the most recent row overall. – GTS Joe Apr 06 '22 at 19:25
Well, can you express what's the difference between the last row and the most recent row overall, since it's hard to distinguish considering the current dataset @GTSJoe – Barbaros Özhan Apr 06 '22 at 19:38

Select Only the Most Recent Row

Query

Output

Desired Output

2 Answers2