LISTING 6: Using analytic queries to aggregate and relate

SQL> select id, cnt, link
  2    from ( select id,
  3  first_value(cnt)  over ( partition by id order by cnt desc ) cnt,
  4  first_value(link) over ( partition by id order by cnt desc ) link,
  5  row_number() over ( partition by id order by cnt desc ) rnum

  6             from t
  7         )
  8   where rnum = 1
  9  /

     ID        CNT       LINK

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