1

I am looking to select the following rows from a table where the following constraints are met:

I have been messing with joins on itself (selecting max version of this if idb = x in a subquery) but have had no luck

For example:

idb = x if va of a specific ida is the max va for that ida and idb is x, then include it

ida     |    idb     |     va
--------------------------------
abc          x             1
abc          y             2
abc          x             3
def          x             1
xyz          x             1
xyz          x             2
xyz          z             3

Result:

ida     |    idb     |     va
--------------------------------
abc          x             3
def          x             1

For the previous example,

abc is included because the highest version of va (3) is in a row where idb=x

def is included because the highest version of va(1) is in a row where idb = x

xyz is NOT included because the highest version of xyz (va=3) has a value of idb=z

MT0
  • 143,790
  • 11
  • 59
  • 117
Dave L
  • 29
  • 1
  • 2
  • The highest version of `va` is also in the last row. I don't understand your conditions. – Gordon Linoff Oct 16 '19 at 14:08
  • Are the `va` values always unique (distinct) for every value of `ida`? Or can there be one (or more) values of `ida` for which there will be duplicate `va` values? It is important to state it, either way, in your problem description. –  Oct 16 '19 at 14:14

2 Answers2

2

If I understand correctly, you want the rows where the maximum version for a given ida has an idb value of 'x'.

If so:

select t.*
from (select t.*,
             max(version) over (partition by ida) as max_version
      from t
     ) t
where version = max_version and idb = 'x';
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

The simplest code would be:

select t.ida, t.idb, t.va
  from your_table t
 where t.idb = 'x'
   and t.va = (select max(sub_qry.va) 
                 from your_table sub_qry
                where sub_qry.ida = t.ida)
Goran Kutlaca
  • 2,014
  • 1
  • 12
  • 18