MySQL Forums
Forum List  »  Newbie

Re: SELECT TOP 3 scores in each class with JOIN
Posted by: Peter Brawley
Date: October 01, 2016 11:39AM

You've not provided the DDL or sample data, so I can't check this, but is this what you mean?

SELECT * FROM 
  (SELECT *, @rn := IF(@prev=class, @rn + 1, 1) AS rn, @prev := class 
   FROM HWData 
   JOIN Awards USING(award)
   JOIN (SELECT @prev := NULL, @rn := 0) AS vars 
   WHERE HWData.score > 0 
     AND HWData.dsqreason = '' 
     AND HWData.class <> '' 
     AND Awards.DSQ <> 1
     ORDER BY class, score DESC 
) AS T1 
WHERE T1.rn <= 3 ;

Options: ReplyQuote


Subject
Written By
Posted
Re: SELECT TOP 3 scores in each class with JOIN
October 01, 2016 11:39AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.