Re: Ranking with partition

From: Yanick Hudon <yannik_at_sympatico.ca>
Date: Sat, 20 Jul 2002 07:54:11 -0700
Message-ID: <Kqc_8.10033$QY4.1913609@news20.bellglobal.com>

Thanks for answering,

    I finally got it in a like manner you describe.     What i did is the following:

    In rhe global query, i performed another ranking view based on GroupeID which is coming

    from a DECODE Statement in the subquery and the code look like this:

break on ClassesMembres.ClasseID skip 2
column Prenom format A15
column Nom format A15
column NomClasse format A10
column ScoreBrut format 999
column Rang format 99
column cmc format 99
column amc format 99
column GroupeID Format A8
SELECT Prenom,

        nom,
        nomclasse,
        ScoreBrut,
        cmc,
        amc,
        RANK() OVER (Partition By GroupeID order by ScoreBrut asc) as Rang
FROM
        (SELECT
                Prenom,
                nom,
                nomclasse,
                ScoreBrut,
                ClassesMembres.classeid cmc,
                Classes.ClasseID amc,
                RANK() OVER (Partition By Classesmembres.classeid order by
ScoreBrut asc) as Rang,
                decode (ClassesMembres.Classeid, 1, 'Groupe 1',
                                                 2, 'Groupe 2',
                                                 3, 'Groupe 3',
                                                 4, 'Groupe 4',
                                                 5, 'Groupe 5',
                                                 6, 'Groupe 6',
                                                 7, 'Groupe 7',
                                                 8, 'Groupe 8',
                                                 9, 'Groupe 9') GroupeID
         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)

And here a fragment of results:

---

• 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

PRENOM NOM NOMCLASSE SCOREBRUT CMC AMC RANG --------------- --------------- ---------- --------- --- --- ---- ****** OK Comes from the global rank() by GroupeID ******* Dario De Obaldia HommeA 88 1 1 12 ****** OK Comes from the global rank() by GroupeID *******

Danny           Collard         HommeA            91   1   1   13
Richard         Daigle          HommeB            83   2   2    1
Jacques         Amyot           HommeB            85   2   2    2
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

PRENOM          NOM             NOMCLASSE  SCOREBRUT CMC AMC RANG
--------------- --------------- ---------- --------- --- --- ----
Jerry           Labrie          HommeB            90   2   2    9
Sylvain         LeBreton        HommeB            93   2   2   11
Ghislain        Guay            HommeB            93   2   2   11
Daniel          Bélanger        HommeB            95   2   2   13
Francois        Laxton          HommeB            95   2   2   13
Robin           Deschenes       HommeB            95   2   2   13

C

Marc Blum <marc_at_marcblum.de> wrote in message news:fvjijugocn7ojh8320ejou67aijktfqtqs_at_4ax.com...
> On Fri, 19 Jul 2002 23:04:32 -0700, "Yanick Hudon" <yannik_at_sympatico.ca>
wrote:
>
> >
>
> how about wrapping another ranking view, (PARTITION BY CMC ORDER BY RANG),
> around your actual query?
>
> As long as your base tables are not in the 7th magnitude, you can do quite
> spectacular things with analytic functions wrapped around in several
layers.
>
>
> Marc Blum
> mailto:marc_at_marcblum.de
> http://www.marcblum.de
Received on Sat Jul 20 2002 - 09:54:11 CDT