WITH

cte AS (

    SELECT id_X, foo, bar,

           ROW_NUMBER() OVER (PARTITION BY id_X ORDER BY foo DESC) rn

    FROM sourcetable

    )

SELECT id_X, foo, bar

FROM cte

WHERE rn = 1;

SELECT t1.id_X, t1.foo, t1.bar

FROM Table1 t1

INNER JOIN (

  SELECT id_X, MAX(foo) AS max_foo

  FROM Table1

  GROUP BY id_X

) t2 ON t1.id_X = t2.id_X AND t1.foo = t2.max_foo

ORDER BY t1.id_X;

SELECT * FROM table GROUP BY id_X ORDER BY foo DESC