• 0

[MySQL] Basic query help (Aggregate functions)


Question

create table student (s_no int primary key, name varchar(20), class varchar(20))
engine=InnoDB;
create table competition (c_no int primary key, name varchar(20), type varchar(20))
engine=InnoDB;
create table s_c (s_no int, c_no int, rank int, year int, foreign key (s_no) references student (s_no),
foreign key (c_no) references competition (c_no))
engine=InnoDB;

insert into student values (1, "BBB", "7th");
insert into student values (2, "AAA", "9th");
insert into student values (3, "DDD", "8th");
insert into student values (4, "CCC", "10th");
insert into student values (5, "FFF", "8th");
insert into student values (6, "EEE", "10th");

insert into competition values (101, "Running", "Sports");
insert into competition values (102, "Chess", "Edu");

insert into s_c values (2, 102, 1, 2000);
insert into s_c values (1, 102, 3, 2000);
insert into s_c values (3, 102, 2, 2000);
insert into s_c values (5, 102, 1, 2000);
insert into s_c values (4, 102, 3, 2000);
insert into s_c values (2, 102, 2, 2000);
insert into s_c values (1, 102, 1, 2000);
insert into s_c values (3, 102, 2, 2000);
insert into s_c values (4, 101, 1, 2000);
insert into s_c values (3, 101, 2, 2000);
insert into s_c values (5, 101, 1, 2000);
insert into s_c values (3, 101, 3, 2000);
insert into s_c values (6, 101, 2, 2000);
insert into s_c values (1, 101, 1, 2000);
insert into s_c values (4, 101, 2, 2000);
insert into s_c values (3, 101, 3, 2000);

select * from student;
select * from competition;
select * from s_c;

Ok so that's the code I've written till now.. As you can see s_c table represents the many to many relation between student and competition.. The query I'm attempting to do is find the student who has won (i.e. 1st rank) most number of times and am not able to get any solution

select name from student where s_no in (select s_no from s_c where rank=1 ORDER BY rank);

I know the code I'm entering to get the required output isn't exactly what is expected in the question so I'll be glad if anyone would help

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

something like this i think.. i haven't tested this.

SELECT s_no COUNT(1) AS wins FROM s_c WHERE rank = 1 GROUP BY s_no ORDER BY wins DESC

that should get you the student number at least. from there you should be able to join the other tables with the information you want to retrieve and add the fields to the group by clause.

Link to comment
Share on other sites

  • 0

Hey thanks for the reply.. I'll try it and get back to you..

EDIT: Got it to work.. Thanks for the tip.. Here's the modified one that I used

SELECT s_no FROM s_c WHERE rank = 1 GROUP BY s_no ORDER BY count(c_no) DESC LIMIT 1;

Edited by SVG
Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.