question

Janvi avatar image
Janvi asked

I'm Unable to write this below query to display the multiple toppers in alphabetical order.

Write a query to display the name(s) of the students who have secured the maximum marks in each subject, ordered by subject name in ascending order. If there are multiple toppers, display their names in alphabetical order.

Display it as subject_name and student_name.
O/P: first column - subject_name
second column - student_name

queryjoinsrdbms
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

do you have table definitions with sample data? that would really help us to help you.

what have you tried so far? can you find the toppers without ordering them? order them without finding the toppers?

0 Likes 0 ·

Hi, @KenJ, I'm attaching the table definitions below.

img-20190325-055937.jpg

0 Likes 0 ·

can you post the query you are working on? for example

select 
    Department.department_name, 
    Student.student_name
from Student
inner join Department on Student.department_id = Department.department_id

would list departments and the students within them - not what you are looking for, but just a quick example of joining two tables. Do you have anything similar started?

0 Likes 0 ·
Janvi avatar image
Janvi answered

create table t as (select s.subject_name, min(m.value) as min_mark

from subject s inner join mark m on s.subject_id=m.subject_id

inner join student su

on su.student_id=m.student_id

group by subject_name)

order by subject_name desc;

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Janvi avatar image
Janvi answered

@KenJ this is all that I tried. But I'm unable to compute the result. Please help me get the required result.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

prateek avatar image
prateek answered

SELECT subject_name,student_name from

Student s inner join Mark m on s.student_id=m.student_id

inner join Subject su on m.subject_id=su.subject_id

inner join (select subject_id,max(value) as maximum from Mark ma group by subject_id) highmarks ON highmarks.subject_id=m.subject_id AND highmarks.maximum=m.value

order by subject_name ASC,student_name ASC;

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Charan avatar image
Charan answered

hey @prateek ,there's no highmarks in schema ,can i know how we can use it? the query worked but i cant understand how highmarks is taken

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Charan Highmarks is a derived table,


 (select subject_id,max(value) as maximum from Mark ma group by subject_id) highmarks
0 Likes 0 ·
mridultuteja avatar image
mridultuteja answered
select subject_name, student_name from student inner join mark using(student_id) 
inner join subject using(subject_id) 
where (subject_id, value) in( select subject_id, max(value) from mark inner join subject using(subject_id) group by subject_id ) 
order by subject_name, student_name;
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ahamed avatar image
ahamed answered

@mridultuteja Thank you..

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.