LISTING 5: Getting aggregate and related information

SQL> select id,
  2         to_number( substr( data, 1, 9 ) ) cnt,
  3         to_number( substr( data, 10 ) ) link
  4    from (
  5  select id, max( to_char(cnt,'fm000000009') || link ) data

  6    from t
  7   group by id
  8             )
  9  /

           ID     CNT    LINK

        -----     ---    ----
        40032      1      32
        40033      1      43
        40034      6      22


Another approach to this query could be:

SQL> select id, cnt,
  2       (select link from t t2 where t2.id = t.id and t2.cnt = t.cnt and rownum=1) link

  3   from ( select id, max(cnt) cnt
  4            from t
  5           group by id
  6        ) t
  7  /

     ID           CNT    LINK
        -----     ---    ----
        40032       1      32

        40033       1      43
        40034       6      16