Ranking with partition

Hi to all,

    One question:

    Is it possible to make the ranking continously if a certain criteria match after subpartitioning??

    Example: I want to have the first ten players in order asc according to their score with the first two

                    levels mixed up and after the tenth position, i want
only the first level and a continously
                   ranking.

    Here the fragment of results with the query i made. Look out the comments to see what i mean.

1. My Query
   -- Part 1: Heading
   break on ClassesMembres.ClasseID skip 2 column Prenom format A20 column Nom format A20 column NomClasse format A10 column ScoreBrut format 999 column Rang format 99 column cmc format 99 column amc format 99
   -- Part 2: Global Select with rejecting the record whose i don't need after
   the tenth position SELECT * FROM
   -- Part 3: SubQuery where the ranking is done by mixing up the levels of
   players according to
   -- the levels they can play during tournaments
   (SELECT Prenom, nom, nomclasse, ScoreBrut, ClassesMembres.classeid cmc, Classes.ClasseID amc,
   -- Each record of ClassesMembres give a member with the levels they can play
   RANK() OVER (Partition By Classesmembres.classeid order by ScoreBrut asc) as Rang FROM inscriptionstournois, golfeurs, Adherants, classesmembres, Classes, ResultatsTournois WHERE (Inscriptionstournois.golfeurid = golfeurs.golfeurid AND Adherants.golfeurID = golfeurs.golfeurID AND Adherants.MembreID = classesmembres.MembreID AND Classes.ClasseID = Adherants.ClasseID AND Resultatstournois.golfeurID = Adherants.golfeurID AND ResultatsTournois.EvenementID = InscriptionsTournois.EvenementID AND InscriptionsTournois.Evenementid = 31)) WHERE NOT (Rang > 10 AND cmc <> amc)

   ---

• Résultats --

  ---

  PRENOM NOM NOMCLASSE SCOREBRUT CMC AMC RANG -------------------- -------------------- ---------- --------- --- --- ---- Luc Desgagné HommeA 74 1 1 1 OK Yves Belzile HommeA 78 1 1 2 OK Sylvain Locas HommeA 78 1 1 2 OK Guy Régnier HommeA 80 1 1 4 OK Luc Lauzon HommeA 81 1 1 5 OK Pierre Dutil HommeA 82 1 1 6 OK Ronald Legault HommeA 82 1 1 6 OK Richard Daigle HommeB 83 1 2 8 OK Jean Fortin HommeA 84 1 1 9 OK Jacques Amyot HommeB 85 1 2 10 OK Pascal Arseneault HommeB 85 1 2 10 OK Dario De Obaldia HommeA 88 1 1 15 NOT OK --> I want 12 Danny Collard HommeA 91 1 1 20 NOT OK --> I want 13 Richard Daigle HommeB 83 2 2 1 Second level Jacques Amyot HommeB 85 2 2 2 .... And so on Pascal Arseneault HommeB 85 2 2 2 Denis Carey HommeB 86 2 2 4 Michel Poulin HommeB 87 2 2 5 Christian Roy HommeB 87 2 2 5 Dean Carruthers HommeB 88 2 2 7 Dominic Lavallée HommeB 89 2 2 8 Martin Charlebois HommeB 90 2 2 9 Jerry Labrie HommeB 90 2 2 9 Sylvain LeBreton HommeB 93 2 2 11 Ghislain Guay HommeB 93 2 2 11

Thanks in advance for your responses. Received on Sat Jul 20 2002 - 01:04:32 CDT