ORDER BY column_1 LIMIT 100 , 40

select 
  a
 ,max(b)
 ,max(c) keep(dense_rank first order by b) "Макс. С среди строк с максимальным B"
from t
group by t.a

select
from (select
          t.*
        , dense_rank() over(order by t.c) drnk
        , rank()       over(order by t.c) rnk
        , row_number() over(order by t.c) rn
        , ntile(100)   over(order by t.c) nt
      from t
)
where -- выбираем что хотим:
  -- drnk <= 10 - топ 10 значений + "with ties"
  -- rnk  <= 10 - топ 10 + "with ties"
  -- rn   <= 10 - строго топ 10 строк - аналог limit 0,10
  -- nt   <= 5 - топ 5%

ORDER BY order_by_expression
    [ COLLATE collation_name ] 
    [ ASC | DESC ] 
    [ ,...n ] 
[ <offset_fetch> ]


<offset_fetch> ::=
{ 
    OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
    [
      FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
    ]
}

select 
  a
 ,max(b)
 , (SELECT c FROM t t1 WHERE t1.a= t.a ORDER BY t1.b DESC [...,c desc ] LIMIT 1) с
from t
group by t.a

SELECT 
 a
 ,b
 ,c
FROM 
(select 
  DISTINCT a
from t)  t-- заменить вообще на выборку из источника FK? ,tp dczrjuj chfyjuj  group by-f
,LATERAL 
  (SELECT b, c [,..........] FROM t t1 WHERE t1.a= t.a ORDER BY t1.b DESC [,...] LIMIT 1) foo
            -- "единственный, один и тот же ROW  среди всех строк с максимальным B"