I’ve never really used the analytic functions in anger, so consequently I don’t really have the skills at my fingertips. By way of re-learning them (using Expert One-on-One Oracle | Thomas Kyte | Apress) I’ve been playing about with a list of English Premiership scorers.

The table is:

SQL> desc goals
Name                                      Null?    Type
----------------------------------------- -------- ------------

DIVISION                                  NOT NULL VARCHAR2(20)
END_DATE                                  NOT NULL DATE
PLAYER                                    NOT NULL VARCHAR2(40)
TEAM                                               VARCHAR2(20)
GOALS                                              NUMBER

The third highest scorer for each team, should you want to know that bit of information, is given by:

select * 
from (select team, player, goals, 
row_number() over (partition by team order by goals desc) ranking
from goals)
where ranking = 3

Output is:

TEAM             PLAYER                GOALS RANKING
---------------- --------------------- ----- -------
Arsenal          Robin van Persie          7       3
Aston Villa      Ashley Young              5       3
Birmingham       Sebastian Larsson         4       3
Blackburn        Morten Gamst Pedersen     3       3
Bolton           Kevin Davies              5       3
Burnley          David Nugent              6       3
Chelsea          Florent Malouda          11       3
Everton          Mikel Arteta              5       3
Fulham           Damien Duff               6       3
Hull City        Deiberson Geovanni        3       3
Liverpool        Steven Gerrard            7       3
Man Utd          Antonio Valencia          5       3
Manchester City  Craig Bellamy             9       3
Portsmouth       Nadir Belhadj             3       3
Stoke City       Ricardo Fuller            3       3
Sunderland       Fraizer Campbell          4       3
Tottenham        Niko Kranjcar             6       3
West Ham Utd     Guillermo Franco          4       3
Wigan Athletic   Paul Scharner             4       3
Wolves           Matthew Jarvis            3       3

To further develop this query to return a pivot table, you use a decode on the artificial ‘ranking’ field, as follows:

select team,
max(decode(ranking, 1, player, null)) golden_boot,
max(decode(ranking, 2, player, null)) silver_boot,
max(decode(ranking, 3, player, null)) bronze_boot
from
(select team, player, goals, row_number()
over (partition by team order by goals desc) ranking
from goals)
where ranking <= 3 group by team

This returns:

SQL> /

TEAM             GOLDEN_BOOT         SILVER_BOOT         BRONZE_BOOT
---------------- ------------------- ------------------- -------------------
Arsenal          Francesc Fabregas   Andrey Arshavin     Robin van Persie
Aston Villa      Gabriel Agbonlahor  John Carew          Ashley Young
Birmingham       Cameron Jerome      Lee Bowyer          Sebastian Larsson
Blackburn        David Dunn          Jason Roberts       Morten Gamst Peders
Bolton           Matthew Taylor      Ivan Klasnic        Kevin Davies
Burnley          Steven Fletcher     Graham Alexander    David Nugent
Chelsea          Didier Drogba       Frank Lampard       Florent Malouda
Everton          Louis Saha          Tim Cahill          Mikel Arteta
Fulham           Bobby Zamora        Clinton Dempsey     Damien Duff
Hull City        Stephen Hunt        Jimmy Bullard       Deiberson Geovanni
Liverpool        Fernando Torres     Dirk Kuyt           Steven Gerrard
Man Utd          Wayne Rooney        Dimitar Berbatov    Antonio Valencia
Manchester City  Carlos Tevez        Emmanuel Adebayor   Craig Bellamy
Portsmouth       Aruna Dindane       Frederic Piquionne  Nadir Belhadj
Stoke City       Matthew Etherington Tuncay Sanli        Ricardo Fuller
Sunderland       Darren Bent         Kenwyne Jones       Fraizer Campbell
Tottenham        Jermain Defoe       Peter Crouch        Niko Kranjcar
West Ham Utd     Carlton Cole        Alessandro Diamanti Guillermo Franco
Wigan Athletic   Hugo Rodallega      Charles N'Zogbia    Paul Scharner
Wolves           Kevin Doyle         Jody Craddock       Matthew Jarvis

20 rows selected.