Is a recursive CTE required, just creative window functions, a loop?

Question

I can’t understand for life how to get a weighted rating for dozens of categories. For example, a student must answer 10 questions in three categories (both in the case of questions, and in the # categories will change in the end). To get a total score, the top 1 point in each of the X (3) categories will be added to what is left to add up to 10 full points.

Here are the data. I used CASE WHEN Row_Number () to get TopInCat

http://sqlfiddle.com/#!6/e6e9f/1

The violin has more students.

| Question | Student | Category | Score | TopInCat |
| ---------- | --------- | ---------- | ------- | --------- - |
| 120149 | 125 | 6 | 1 | 1 |
| 120127 | 125 | 6 | 0.9 | 0 |
| 120124 | 125 | 6 | 0.8 | 0 |
| 120125 | 125 | 6 | 0.7 | 0 |
| 120130 | 125 | 6 | 0.6 | 0 |
| 120166 | 125 | 6 | 0.5 | 0 |
| 120161 | 125 | 6 | 0.4 | 0 |
| 120138 | 125 | 4 | 0.15 | 1 |
| 120069 | 125 | 4 | 0.15 | 0 |
| 120022 | 125 | 4 | 0.15 | 0 |
| 120002 | 125 | 4 | 0.15 | 0 |
| 120068 | 125 | 2 | 0.01 | 1 |
| 120050 | 125 | 3 | 0.05 | 1 |
| 120139 | 125 | 2 | 0 | 0 |
| 120156 | 125 | 2 | 0 | 0 |

, , . 10 3 , Sort 1-10 . 999 , , .

| Question | Student | Category | Score | TopInCat | Sort |
|----------|---------|----------|-------|----------|------|
|   120149 |     125 |        6 | 1     |        1 |    1 |
|   120138 |     125 |        4 | 0.15  |        1 |    2 |
|   120068 |     125 |        2 | 0.01  |        1 |    3 |
|   120127 |     125 |        6 | 0.9   |        0 |    4 |
|   120124 |     125 |        6 | 0.8   |        0 |    5 |
|   120125 |     125 |        6 | 0.7   |        0 |    6 |
|   120130 |     125 |        6 | 0.6   |        0 |    7 |
|   120166 |     125 |        6 | 0.5   |        0 |    8 |
|   120161 |     125 |        6 | 0.4   |        0 |    9 |
|   120069 |     125 |        4 | 0.15  |        0 |   10 |
|   120022 |     125 |        4 | 0.15  |        0 |  999 |
|   120002 |     125 |        4 | 0.15  |        0 |  999 |
|   120050 |     125 |        3 | 0.05  |        1 |  999 |
|   120139 |     125 |        2 | 0     |        0 |  999 |
|   120156 |     125 |        2 | 0     |        0 |  999 |

, , X (3) . , 4- .

| Question | Student | Category | Score | TopInCat | Sort |
|----------|---------|----------|-------|----------|------|
|   120149 |     126 |        6 | 1     |        1 |    1 |
|   120138 |     126 |        4 | 0.75  |        1 |    2 |
|   120068 |     126 |        2 | 0.50  |        1 |    3 |
|   120127 |     126 |        6 | 0.9   |        0 |    4 |
|   120124 |     126 |        6 | 0.8   |        0 |    5 |
|   120125 |     126 |        6 | 0.7   |        0 |    6 |
|   120130 |     126 |        6 | 0.6   |        0 |    7 |
|   120166 |     126 |        6 | 0.5   |        0 |    8 |
|   120050 |     126 |        3 | 0.45  |        1 |    9 |********
|   120161 |     126 |        6 | 0.4   |        0 |   10 |
|   120069 |     126 |        4 | 0.15  |        0 |  999 |
|   120022 |     126 |        4 | 0.15  |        0 |  999 |
|   120002 |     126 |        4 | 0.15  |        0 |  999 |
|   120139 |     126 |        2 | 0     |        0 |  999 |
|   120156 |     126 |        2 | 0     |        0 |  999 |

. .

Answer 1

" ". , , , .

,

CREATE TABLE WeightedScores
    ([Question] int, [Student] int, [Category] int, [Score] dec(3,2))
;

INSERT INTO WeightedScores
    ([Question], [Student], [Category], [Score])
VALUES
    (120161, 123, 6, 1),    (120166, 123, 6, 0.64),    (120138, 123, 4, 0.57),    (120069, 123, 4, 0.5),
    (120068, 123, 2, 0.33),    (120022, 123, 4, 0.18),    (120061, 123, 6, 0),    (120002, 123, 4, 0),
    (120124, 123, 6, 0),    (120125, 123, 6, 0),    (120137, 123, 6, 0),    (120154, 123, 6, 0),
    (120155, 123, 6, 0),   (120156, 123, 6, 0),    (120139, 124, 2, 1),    (120156, 124, 2, 1),
    (120050, 124, 3, 0.88),    (120068, 124, 2, 0.87),    (120161, 124, 6, 0.87),    (120138, 124, 4, 0.85),
    (120069, 124, 4, 0.51),    (120166, 124, 6, 0.5),    (120022, 124, 4, 0.43),    (120002, 124, 4, 0),
    (120130, 124, 6, 0),    (120125, 124, 6, 0),    (120124, 124, 6, 0),    (120127, 124, 6, 0),
    (120149, 124, 6, 0),    (120149, 125, 6, 1),    (120127, 125, 6, 0.9),    (120124, 125, 6, 0.8),
    (120125, 125, 6, 0.7),    (120130, 125, 6, 0.6),    (120166, 125, 6, 0.5),    (120161, 125, 6, 0.4),
    (120138, 125, 4, 0.15),    (120069, 125, 4, 0.15),    (120022, 125, 4, 0.15),    (120002, 125, 4, 0.15),
    (120068, 125, 2, 0.01),    (120050, 125, 3, 0.05),    (120139, 125, 2, 0),    (120156, 125, 2, 0),
    (120149, 126, 6, 1),    (120138, 126, 4, 0.75),    (120068, 126, 2, 0.50),    (120127, 126, 6, 0.9),
    (120124, 126, 6, 0.8),    (120125, 126, 6, 0.7),    (120130, 126, 6, 0.6),    (120166, 126, 6, 0.5),
    (120050, 126, 3, 0.45),    (120161, 126, 6, 0.4),    (120069, 126, 4, 0.15),    (120022, 126, 4, 0.15),
    (120002, 126, 4, 0.15),    (120139, 126, 2, 0),    (120156, 126, 2, 0)
;

.

---

; , , , . .

, , :

;WITH Numbered1 ( Question, Student, Category, Score, SeqInStudentCategory ) AS
(
    SELECT Question, Student, Category, Score
        , ROW_NUMBER() OVER (PARTITION BY Student, Category ORDER BY Score DESC) SeqInStudentCategory 
    FROM WeightedScores
)

, SeqInStudentCategory 1. , :

-- within the preceding WITH
, Numbered2 ( Question, Student, Category, Score, SeqInStudent ) AS
(
    SELECT 
        Question, Student, Category, Score
        , ROW_NUMBER() OVER (PARTITION BY Student ORDER BY Score DESC) SeqInStudent
    FROM
        Numbered1
    WHERE
        SeqInStudentCategory = 1
)

, SeqInStudent 3. , , ( , , ):

-- within the preceding WITH
, TopInCat ( Question, Student, Category, Score, SeqInStudent ) AS
(
     SELECT Question, Student, Category, Score, SeqInStudent FROM Numbered2 WHERE SeqInStudent <= 3
)

. , :

-- within the preceding WITH
, NotTopInCat ( Question, Student, Category, Score, SeqInStudent ) AS
(
    SELECT
        Question, Student, Category, Score
        , ROW_NUMBER() OVER (PARTITION BY Student ORDER BY Score DESC) SeqInStudent
    FROM
        WeightedScores WS
    WHERE
        NOT EXISTS ( SELECT 1 FROM TopInCat T WHERE T.Question = WS.Question AND T.Student = WS.Student )
)

, TopInCat NotTopInCat, NotTopInCat.SeqInStudent - 3 7 ( 10 - 3):

-- within the preceding WITH
, Combined ( Question, Student, Category, Score, CombinedSeq ) AS
(
    SELECT
        Question, Student, Category, Score, SeqInStudent AS CombinedSeq
    FROM
        TopInCat
    UNION
    SELECT
        Question, Student, Category, Score, SeqInStudent + 3 AS CombinedSeq
    FROM
        NotTopInCat
    WHERE
        SeqInStudent <= 10 - 3
)

:

SELECT * FROM Combined ORDER BY Student, CombinedSeq
;

sqlfiddle.

---

, , . , TopInCat, , , , , .

, "( , " ), . , ( ) 3 .