Ranking

Jimbob7 · Sep 27, 2005

I've got a football table ranked by Points and Goal Difference:

e.g.

Team Pts Goal Diff
C 4 +2
A 4 +1
B 0 -3

and I want to introduce another field automatically (in a query) which gives the position of each team:

Pos Team Pts Goal Diff
1 C 4 +2
2 A 4 +1
3 B 0 -3

Can anyone help?

Thanks a lot

Jimbob

llkhoutx · Sep 27, 2005

Query the records in the order you want; then query that query creating a new field via a function which create a counter indicating the "Pos." Of course, you have to be able to reset the counter each time prior to rerunning the 2nd query.

Jimbob7 · Sep 27, 2005

Ok thanks, but how do I create the "counter" function?

SMatthews · Sep 27, 2005

Try this:

SELECT Team, Points, GoalDiff,
(SELECT Count(*)+1 FROM tblTeams t1 where t1.points > t.points or (t1.points >= t.points and t1.goaldiff > t.goaldiff)) as Position
FROM tblTeams t
ORDER BY t.Points DESC , t.GoalDiff DESC;

It may not look pretty, but it seems to work pretty good.

Jimbob7 · Sep 28, 2005

That's excellent, thanks to both of you for your help.

Jimbob7 · Sep 28, 2005

Just one more thing... I've attempted to adapt the code slightly so that if teams are level on points and goal difference they are then ranked by the number of times they've scored. However, in the example below, teams C and E are both given a postion of 2 (rather than C being 2 and E being 3):

Table:

Team Points GoalDiff GoalsFor
A 3 -1 1
B 0 -4 0
C 4 2 4
D 2 0 1
E 4 2 3
F 6 0 3

Code:

SELECT (SELECT Count(*)+1 FROM tblTeams t1 where t1.points > t.points or (t1.points >= t.points and t1.goaldiff > t.goaldiff) or (t1.points >= t.points and t1.goaldiff > t.goaldiff and t1.goalsfor >= t.goalsfor)) AS [Position], t.Team, t.Points, t.GoalDiff, t.GoalsFor
FROM tblTeams AS t
ORDER BY t.Points DESC , t.GoalDiff DESC , t.GoalsFor DESC , t.Team;

Can anyone help?

Thanks

James

SMatthews · Sep 28, 2005

This is basically right. You just had your ">=" and ">" backwards in the last OR statement.

SELECT (SELECT Count(*)+1 FROM tblTeams t1 where t1.points > t.points or (t1.points >= t.points and t1.goaldiff > t.goaldiff) or (t1.points >= t.points and t1.goaldiff >= t.goaldiff and t1.goalsfor > t.goalsfor)) AS [Position], t.Team, t.Points, t.GoalDiff, t.GoalsFor
FROM tblTeams AS t
ORDER BY t.Points DESC , t.GoalDiff DESC , t.GoalsFor DESC , t.Team;

Jimbob7 · Sep 28, 2005

That's great, thanks once again.

Ranking

Jimbob7

New member

llkhoutx

Registered User.

Jimbob7

New member

SMatthews

Registered User.

Jimbob7

New member

Jimbob7

New member

SMatthews

Registered User.

Jimbob7

New member

Similar threads

Users who are viewing this thread