How to select only ranks which occur once except the first rank?

Question

I have 2 tables Table1: student

Table2: problem

Now we need to find out which student has solved how many problems and in the output we need to display student_id, student_name and no_of_prob in descending order of no_of_prob.

If more than one student has equal number of problems then 1. If the no_of_prob is highest among all others then keep all of them. 2. If the no_of_prob is not the highest, then do not keep any of these. For example, when we group by student_id, we get this

As student_id 2 and 5 have equal no_of_prob but as their no_of_prob is highest, we will keep both student_id 2 and 5. But the student_id 1 and 3 have equal no_of_prob but its not the highest therefore we need to drop both of these. The final table should be like this.

Now i have found out how to get till the table number 3 above but I don't know how am I supposed to keep the 1st rankers but remove lower rankers if repeated. This is my code below

select s.student_id,student_name, count(problem_id) as no_of_prob,
rank() over(order by count(problem_id) desc) as st_rank
from student s inner join problem p on s.student_id = p.student_id
group by s.student_id;

You have dumped external image links into your question, which contain critical data relating to your problem. Please _delete_ those links, and instead take a few minutes to make this a complete question by including all data directly in your question, as _text_. — Tim Biegeleisen, Mar 09 '20 at 05:57
this might help: https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql. You might want to change `desc` to `asc` for ordering in the st_rank. — trk, Mar 09 '20 at 06:09
@TimBiegeleisen I am new to stackoverflow and I copied the image and pasted it while writing the question but after hitting submit, its showing like that. I don't know how to change that. — Sharad Sharma, Mar 09 '20 at 06:24
@TimBiegeleisen It says that I need to have at least 10 reputation to post pictures. — Sharad Sharma, Mar 09 '20 at 06:31
Edit your post and add the tables as **"text"** instead of images. — Arun Palanisamy, Mar 09 '20 at 07:15

Arun Palanisamy · Answer 1 · 2020-03-09T13:46:10.383

You can use the RANK() function to get the 1st rankers along with rows having count as 1 with the subquery.

SELECT student_id, 
       student_name, 
       no_of_prob 
FROM   (SELECT p.student_id,student_name, 
               COUNT(*) AS no_of_prob, 
               RANK() OVER(ORDER BY COUNT(*) DESC) rk 
        FROM   problem p 
               JOIN student s ON p.student_id = s.student_id 
        GROUP  BY p.student_id,student_name 
        ORDER  BY COUNT(*) DESC) a 
WHERE  rk = 1 OR no_of_prob = 1

Check Demo Here

Output

score 0 · Answer 2 · answered Mar 09 '20 at 06:41

Try this query. Hopefully it will be work fine.

SELECT p.student_id,s.student_name,count(*) as no_of_prb 
FROM `problem` p 
left join student s on p.student_id=s.student_id 
group by p.`student_id` 
having 
     no_of_prb not in (select count(*) as total from problem group by student_id having total>1)
     OR 
     no_of_prb = (select count(*) as total from problem group by student_id order by total desc limit 1)
order by no_of_prb desc

I answered you for this case. – Tushar Mar 09 '20 at 10:23 — Tushar, Mar 09 '20 at 10:23

How to select only ranks which occur once except the first rank?

2 Answers2