John John John John - 3 months ago 8
SQL Question

Max version with both values of another Column

I want to present the last version of a table with both values on another.

Version | Value
12 | ORANGE
11 | ORANGE
11 | BANANA
10 | ORANGE
11 | BANANA


The result should be

11 | ORANGE
11 | BANANA


Can someone help me?

Answer

This should works

SELECT * 
 FROM TABLE
 WHERE VERSION IN (
    SELECT MIN(VERSION) -- GET MIN VERSION FROM MAX
    FROM (
       SELECT MAX(VERSION) as VERSION, 
       FROM TABLE
       GROUP BY VALUE)
    )
  )

This trick return you min version from Max versions, it works only if both banana and orange share exact version number. This means that if Orange skips a versione (from 11 to 13 withou 12) and banana is exactly 12, it won't work.