CODEWITHSUNDEEP
sqldatabasesqlite
fandang
fandang

Reputation: 607

How can I update a "rank" column's values based on the values of another column?

I have an SQLite database table GOALS_SCORED with columns gender, goalRank and numGoalsScored.

The numGoalsScored is set, but I want to set the goalRank PER GENDER to 1, 2, 3, etc.

So I want 2 values that are goalRank 1 (for M and F). Just for now, assume that no 2 boys have the same # of goals, same for girls. What's the SQL to update that column with the right values?

Upvotes: 0

Views: 205

Answers (1)

Dan Kennedy
Dan Kennedy

Reputation: 538

UPDATE goals_scored SET goalRank = (
  SELECT count(*) FROM goals_scored AS i WHERE 
    i.numGoalsScored > goals_scored.numGoalsScored AND
    i.gender = goals_scored.gender
) + 1;

is one way. O(n^2) though.

Upvotes: 2

Related Questions