CODEWITHSUNDEEP
sqlsql-server
Nody
Nody

Reputation: 85

Get top n rows from table in sql server

So let me paste both table schema first

CREATE TABLE Segment_Master ( [segment_id] bigint, [subject_code_id] bigint, [segment_name] nvarchar(60), [segment_description] nvarchar(250), [must_attend_question] tinyint, [total_question] tinyint, [branch_id] bigint, [entry_by] bigint, [entry_date] datetime, [test_id] int, [neg_marks_each_quest] decimal(4,2) )
INSERT INTO Segment_Master
VALUES
( 1, 1, N'First Segment', N'First Segment Description', 5, 5, 15, 10238, N'2018-05-16T13:03:17.583', 1, 0.50 ), 
( 2, 1, N'Second Section', N'Second Segment', 5, 6, 15, 10238, N'2018-05-16T13:03:17.583', 1, 0.00 ), 
( 3, 1, N'Third Segment', N'Third Segment', 1, 2, 15, 10238, N'2018-05-16T13:03:17.583', 1, 0.00 )

Now let me show second table..

CREATE TABLE OnlineTestAnswer ( [auto_id] bigint, [segment_id] int, [question_id] int, [marks] decimal(9,4), [student_id] int, [test_id] int, [branch_id] int, [faculty_id] int )
INSERT INTO OnlineTestAnswer
VALUES
( 1, 1, 1, 1.0000, 10246, 1, 15, 10246 ), 
( 2, 1, 31, -0.5000, 10246, 1, 15, 10246 ), 
( 3, 1, 32, -0.5000, 10246, 1, 15, 10246 ), 
( 4, 1, 33, -0.5000, 10246, 1, 15, 10246 ), 
( 5, 1, 34, 0.3700, 10246, 1, 15, 10246 ), 
( 6, 2, 2, 0.0000, 10246, 1, 15, 10246 ), 
( 7, 2, 8, 2.0000, 10246, 1, 15, 10246 ), 
( 8, 2, 31, 0.0000, 10246, 1, 15, 10246 ), 
( 9, 2, 35, 1.0000, 10246, 1, 15, 10246 ), 
( 10, 2, 21, 2.0000, 10246, 1, 15, 10246 ), 
( 11, 2, 22, 2.0000, 10246, 1, 15, 10246 ), 
( 12, 3, 15, 3.5000, 10246, 1, 15, 10246 )

Now If I run select query on these tables..

enter image description here

Now, If you could able to see without magnifying glass :D, there are two columns: must_attend_question and total_question and as name suggests must_attend_question<=total_question Now let me run another table,

enter image description here

Now here comes the story, Suppose A is a segment and it has 5 no. of question in which 4 questions are mandatory.

Now , if I gave all 5 answer then top 4 must be counted.. i.e If you look at the second image , the maximum no. of marks from segment 2 will be 7. (2+2+2+1+0) Note. here top 5 will be counted..

Output that I want w.r.t those two images,

segment_id|marks
----------|------ 
    1     | -.13
----------|------
   2      |  7
----------|-----
   3      |  3.5

Upvotes: 4

Views: 168

Answers (2)

DhruvJoshi
DhruvJoshi

Reputation: 17126

Solution using row_number and inner query See working demo

select O.segment_id,
total=Sum(O.marks)
from 
(
select segment_id,marks,
rn=row_number() over(partition by segment_id order by marks desc)
from OnlineTestAnswer) O
join Segment_Master M
on M.segment_id=O.segment_id and O.rn<=M.must_attend_question
group by O.segment_id

Upvotes: 2

EzLo
EzLo

Reputation: 14189

You can use a CROSS APPLY to get the top N dynamically while ordering by marks, then group by each segment.

SELECT
    T.segment_id,
    SumTopMarks = SUM(S.marks)
FROM
    Segment_Master AS T
    CROSS APPLY (
        SELECT TOP (T.must_attend_question)
            O.*
        FROM
            OnlineTestAnswer AS O
        WHERE
            T.segment_id = O.segment_id
        ORDER BY
            O.marks DESC
    ) AS S
GROUP BY
    T.segment_id

If you have a Segment_Master that doesn't have answers and you want it listed, you can change CROSS APPLY for OUTER APPLY.

Upvotes: 4

Related Questions