1

I do have a table where changes are always inserted. For this purposes, a version number is increased on each insert but one version is always set manually to be the active one. I would now like to retrieve all rows where the row with the highest version does not have 'active'=true.

| UID | Version | Active | Name  |
----------------------------------
| 001 |       1 | _      | Peter |
| 001 |       2 | X      | Paul  |
| 001 |       3 | _      | Petra |
| 002 |       1 | _      | Walt  |
| 002 |       2 | X      | Jan   |
| 003 |       1 | X      | Brad  |

The result of the query should only return uid 001 as there is a max version of 3 which is not active.

I assume that I need some kind of self-join?

Thanks for any help on this!

AntonSack
  • 1,021
  • 2
  • 25
  • 47
  • You don't need a self-join. Get the row with the highest version, then filter it with `HAVING active != 'X'` to remove the active ones. – Barmar Jan 04 '19 at 20:18
  • See https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 for how to get the row with the max version. – Barmar Jan 04 '19 at 20:19

1 Answers1

1

You could use subquery and inner join 

select uid, version
from my_table m
inner join  (
  select  uid, max(version) max_version
  from my_table 
  group by uid
) t ON t.uid = m.uid and t.max_version = m.version 
where active ='-'
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107