It’s coming up to that time of year once more. Games are running out, permutations are being permutated, bottoms are beginning to squeak.
The question on everyone’s lips ( well, Simon’s actually) is, who will fill the Play-Off places in the Conference ?
For those of you not acquainted with the lower echelons of English Professional Football ( or Soccer, if you really must), The Conference is the fifth tier of English football and the pinnacle of non-League. Each year, the winners gain automatic promotion to the promised land of the Football League with those finishing second to fifth playing-off for the right to join them.
All of which gives me the perfect opportunity to forsake my old habits and bring my SQL bang up-to-date.
The (league) Table
Often, I’ll find myself trying to get details of the latest record in a table, using the synthetic key :
SELECT some_data FROM some_table WHERE id = ( SELECT MAX(id) FROM some_table) /
Top of the pile or RANK and file
Obviously, that’s all well and good when you’re dealing with a unique value, but what happens in the hurly burly of the league run-in ….let’s start with some test data :
CREATE TABLE league_table ( team VARCHAR2(30), played NUMBER(2), goal_diff NUMBER(3), points NUMBER(3)) / DECLARE PROCEDURE ins( p_team VARCHAR2, p_pld NUMBER, p_gd NUMBER, p_pts NUMBER) IS BEGIN INSERT INTO league_table( team, played, goal_diff, points) VALUES( p_team, p_pld, p_gd, p_pts); END ins; BEGIN ins('HISTON', 38, -32, 27); ins('EASTBOURNE B', 39, -35, 29); ins('FOREST GREEN', 40, -21, 38); ins('HAYES', 40, -30, 39); ins('ALTRINCHAM', 40, -35, 40); ins('SOUTHPORT', 39, -8, 41); ins('TAMWORTH', 40, -16, 43); ins('BARROW', 39, -12, 43); ins('CAMBRIDGE', 40, -7, 44); ins('GATESHEAD', 37,3,49); ins('MANSFIELD', 36,3,49); ins('KETTERING', 40,-7,50); ins('BATH CITY', 38,-2,52); ins('RUSHDEN', 38,7,53); ins('GRIMSBY', 38,9,53); ins('NEWPORT', 39,5,54); ins('DARLINGTON', 37,15,55); ins('YORK', 38,5,61); ins('FLEETWOOD', 40,16,63); ins('KIDDERMINSTER', 39, 15, 65); ins('WREXHAM', 40,14,70); ins('LUTON', 37, 36, 70); ins('WIMBLEDON', 40, 27, 74); ins('CRAWLEY', 38, 50, 87); END; /
And now, let’s see whose top…
SELECT team, played, points, goal_diff FROM league_table t1 WHERE t1.points = ( SELECT MAX(t2.points) FROM league_table t2) /
OK, at the moment, we’ll only get one record back ( Crawley are running away with the league this year), but let’s indulge Simon’s fantasy for a moment ( no, not that one !) :
UPDATE league_table SET points = 87 WHERE team = 'LUTON' /
Now when we re-run the query we get :
TEAM PLAYED POINTS GOAL_DIFF -------------------- ---------- ---------- ---------- LUTON 37 87 36 CRAWLEY 38 87 50
Now, in the olden days, you’d have to try something like this to get the correct order ( when teams are level on points, the one with the highest goal difference is top) :
SELECT team, played, points, goal_diff FROM ( SELECT team, played, points, goal_diff FROM league_table ORDER BY points DESC, goal_diff DESC) WHERE rownum = 1 /
Not exactly a thrilling prospect. You need to make sure that the rownum reference happens outside of the sub-query as otherwise, the rownum will get applied before the sort and who knows what result you’ll get. Instead, let’s try that swanky new RANK analytical function :
SELECT team, played, points, goal_diff FROM ( SELECT team, played, points, goal_diff, RANK() OVER (ORDER BY points desc, goal_diff DESC) as position FROM league_ table ) WHERE position = 1 /
OK, you’re thinking, it all looks very flash, but what’s wrong with sticking with good old rownum ?
Well, to answer that one, we need to shatter Simon’s illusions once more and look at the grim reality of who is actually in the play-offs. Having rolled back our update, Luton are now back on 70 points. Now, let’s see if we can persuade rownum to tell us who is in the play-off picture…
SELECT team, played, points, goal_diff FROM ( SELECT team, played, points, goal_diff FROM league_table ORDER BY points DESC, goal_diff DESC) WHERE rownum BETWEEN 2 AND 5 / no rows selected
Er…apparently rownum hasn’t kept up-to-date with the comparatively modern concept of play-offs in the Conference.
In order to calm Simon’s nerves a little, I think we need to go back to RANK :
SELECT team, played, points, goal_diff FROM ( SELECT team, played, points, goal_diff, RANK() OVER( ORDER BY points DESC, goal_diff DESC) as position FROM league_table) WHERE position BETWEEN 2 AND 5 / TEAM PLAYED POINTS GOAL_DIFF -------------------- ---------- ---------- ---------- WIMBLEDON 40 74 27 LUTON 37 70 36 WREXHAM 40 70 14 KIDDERMINSTER 39 65 15
We’ll they’re not top, but at least Luton look fairly solid for a play-off place.