3

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.

2 Answers 2

4

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.

0
4

I think this will give you want you want:

 select project_id,
        version,
        stop,
        t_id,
        status,
        mp,
        case when rowno = 1 then 'Y' else 'N' end is_last_version
        from (
              select project_id,
                     version,
                     stop,
                     t_id,
                     status,
                     mp,
                     row_number() over (partition by projecT_id order by version desc) 
                        as rowno
              from t_proj_f t
              order by project_id) 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.