Oracle Query – How to Get Field with Max Value and Display It

oraclequery

I need to display Y or N when displaying rows in a report based on the max value for them on their version. If they have multiple versions all of them will display 'N' but the max (last version) will display 'Y'.

I have tried with case when and max but always I'm getting 'Y'

case when version = (select max(version) from t_proj_f where version = t.version) then 'Y' else 'N' end Is_last_version

Here is fiddle in case anybody can take a look
http://sqlfiddle.com/#!4/813501/4

I'm new on oracle sql any help is appreciated.

Best Answer

Analysis

Your inner SQL statement is always TRUE (for non-null values). You are probably wanting the max(version) for each project_id, not each version.

Additionally, these scalar sub-queries are known to be slow at times.

Solution

Use an Analytical function.

PLAN vs

Yours, right side, is doing two Full Table Scans vs my one. This becomes worse with larger tables. enter image description here

Code

select t.project_id,
  t.version,
  t.stp,
  t.t_id,
  t.status,
  t.mp,
  case
    when t.version = max(t.version) over (partition by t.project_id)
    then 'Y'
    else 'N'
   end Is_last_version
from t_proj_f t

code review note: always use the appropriate table alias on ALL columns, especially if the column could be misinterpreted.

Related Question