Ну и опять же, на НЕУНИКАЛЬНОМ поле Х результаты запросов будут разными - с ROW_NUMBER() гарантированно вернется одна строка по каждой секции. С MAX() же - столько строк на каждую секцию, сколько найдется одинаковых максимумом по полю Х:
--
-- Тестовые данные:
with
t as
(
select 1 as part_id, 1 as x, 'bla-bla-bla-11' as any_column from dual union all
select 1 as part_id, 2 as x, 'bla-bla-bla-12' as any_column from dual union all
select 1 as part_id, 3 as x, 'bla-bla-bla-13' as any_column from dual union all
select 2 as part_id, 1 as x, 'bla-bla-bla-21' as any_column from dual union all
select 2 as part_id, 2 as x, 'bla-bla-bla-22' as any_column from dual union all
select 2 as part_id, 3 as x, 'bla-bla-bla-23' as any_column from dual union all
select 2 as part_id, 3 as x, 'bla-bla-bla-24' as any_column from dual union all
select 3 as part_id, 1 as x, 'bla-bla-bla-31' as any_column from dual union all
select 3 as part_id, 1 as x, 'bla-bla-bla-32' as any_column from dual union all
select 3 as part_id, 1 as x, 'bla-bla-bla-33' as any_column from dual
)
--
-- Основной запрос:
select *
from (
select t.*,
row_number()
over(partition by part_id
order by x desc) as rn
from t
)
where rn = 1
Query finished, retrieving results...
PART_ID X ANY_COLUMN RN
------- --- -------------- --
1 3 bla-bla-bla-13 1
2 3 bla-bla-bla-23 1
3 1 bla-bla-bla-31 1
3 row(s) retrieved
--
-- Тестовые данные:
with
t as
(
select 1 as part_id, 1 as x, 'bla-bla-bla-11' as any_column from dual union all
select 1 as part_id, 2 as x, 'bla-bla-bla-12' as any_column from dual union all
select 1 as part_id, 3 as x, 'bla-bla-bla-13' as any_column from dual union all
select 2 as part_id, 1 as x, 'bla-bla-bla-21' as any_column from dual union all
select 2 as part_id, 2 as x, 'bla-bla-bla-22' as any_column from dual union all
select 2 as part_id, 3 as x, 'bla-bla-bla-23' as any_column from dual union all
select 2 as part_id, 3 as x, 'bla-bla-bla-24' as any_column from dual union all
select 3 as part_id, 1 as x, 'bla-bla-bla-31' as any_column from dual union all
select 3 as part_id, 1 as x, 'bla-bla-bla-32' as any_column from dual union all
select 3 as part_id, 1 as x, 'bla-bla-bla-33' as any_column from dual
)
--
-- Основной запрос:
select *
from (
select t.*,
max(x) over(partition by part_id
order by x desc) as max_x
from t
)
where x = max_x
Query finished, retrieving results...
PART_ID X ANY_COLUMN MAX_X
------- --- -------------- -----
1 3 bla-bla-bla-13 3
2 3 bla-bla-bla-24 3
2 3 bla-bla-bla-23 3
3 1 bla-bla-bla-31 1
3 1 bla-bla-bla-32 1
3 1 bla-bla-bla-33 1
6 row(s) retrieved