jobobo jobobo - 8 days ago 6
SQL Question

sqlite subquery for group of ranked items

The table has three columns (Group, Rank, Score), with composite primary key (Group,Rank). Each Group has at least one entry with Rank of 1 and a score. If more than one row per Group, it will have a rank greater than one and a score. For example:

+-------+------+-------+
| Group | Rank | Score |
+-------+------+-------+
| 1 | 1 | 100 |
| 1 | 2 | 99 |
| 1 | 3 | 80 |
| 2 | 1 | 70 |
| 2 | 2 | 68 |
| 2 | 3 | 50 |
| 2 | 4 | 20 |
| 3 | 1 | 80 |
+-------+------+-------+


My goal is to pick all rows "close" to each Group's top score, where top score is defined to be score of Rank value of 1 per Group. In the above example, Group 1 has top score of 100, Group 2 has top score of 70, Group 3 has top score of 80. We want to choose all rows that are at most 5% smaller than the top scores. For the above example, the resulting selection would return:

+-------+------+-------+
| Group | Rank | Score |
+-------+------+-------+
| 1 | 1 | 100 |
| 1 | 2 | 99 |
| 2 | 1 | 70 |
| 2 | 2 | 68 |
| 3 | 1 | 80 |
+-------+------+-------+


My second goal is to place the ratio of score/top_score as a new column, which would obviate the above goal.
Thanks in advance. For SQLite.

Answer

Untested, off of the top of my head.

SELECT s."Group"
  , s."Rank"
  , s."Score"
  , s."Score"/ts."Score" as "ScoreRatio"
FROM scores s
  JOIN (
      SELECT "Group", "Score"
      FROM scores
      WHERE "Rank" = 1
  ) ts
  ON s."Group" = ts."Group"
    AND 0.95 * ts."Score" <= s."Score"
Comments