jobobo - 1 year ago 85

SQL Question

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 Source

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"
```