Look into the Ordered Analytical Functions specifically the window aggregates of MAX and COUNT.
Attachments
Library
Quick Reference
Archives
Sample Index
2006 Indexes