CODEWITHSUNDEEP
oracle-database
ijustdontget it
ijustdontget it

Reputation: 21

To display student id and the minimum mark scored by that students in any subject.Give an alias as minimum_mark. Sort the result based on minimum_mark

enter image description here

data in mark table:

VALUE      SUBJECT_ID STUDENT_ID                                                
---------- ---------- ----------                                                
    73          1          6                                                
    98          1          1                                                
    68          2          1                                                
    75          3          6                                                
    78          5          6                                                
    75          1          7                                                
    69          4          7

I have tried the following code, it executes successfully and gives the desired result, however it fails to clear one test case and IDK why?

SELECT DISTINCT
    student_id,
    min(value) as MINIMUM_MARK 
FROM mark
WHERE value IN (
    SELECT
        min(value)
    FROM mark
    GROUP BY (subject_id)
)
GROUP BY (student_id)
ORDER BY MINIMUM_MARK;

The expected output is : 

STUDENT_ID MINIMUM_MARK
---------- ------------
     1       68
     7       69
     6       73

Upvotes: 0

Views: 6554

Answers (2)

Popeye
Popeye

Reputation: 35910

You can simply use group by as following:

Select student_id,
       Min(value) as minimum_marks
From marks
Group by student_id
Order by minimum_marks

Cheers!!

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521279

I would just use ROW_NUMBER here:

WITH cte AS (
    SELECT m.*, ROW_NUMBER() OVER (PARTITION BY STUDENT_ID ORDER BY "VALUE" DESC) rn
    FROM mark
)

SELECT STUDENT_ID, MARK
FROM cte
WHERE rn = 1
ORDER BY MARK;

Upvotes: 0

Related Questions