Re: Examination

I'm hoping that you aren't asking for an exam answer for school. I'll make the assumption you are not, despite the title:

If I understand the problem statement correctly... [return?] "best mark for each year for each teacher".

That's the key! Your query came close to solving the problem, but the subquery returning the maximum mark, summarized per teacher and year, does not correlate those values to values in the main higher level query block (idteacher and year). A consequence of this will be that a side affect occurs when another teacher who has the same mark as another teacher's highest mark (not necessessarily his or her highest!), regardless of the year, will also be listed. A slight modification will do it for you.

/* modification of poster's query */
SELECT Year, idteacher, IdThesis
 FROM (SELECT IdThesis, IdTeacher, Mark, Year

         FROM DegreeThesis base
         WHERE IdTeacher IN (SELECT IdTeacher
                               FROM (SELECT IdTeacher , COUNT(*)
                                      FROM DegreeThesis
                                           WHERE Mark= 0
                                      GROUP BY IdTeacher
                                      HAVING COUNT(*)>=2)
                            )
          AND Mark IN (SELECT Max(Mark)   /* simplified */
                                 FROM DegreeThesis
                                 WHERE Mark<> 0
                                   AND base.idteacher = idteacher  /*
correlation to upper level query */
                                   AND base.year = year                /*
correlation to upper level query */
                                 GROUP BY IdTeacher , Year

Alternatively, using existential quantifiers...

SELECT year, idteacher, idthesis
  FROM degreethesis a
  WHERE EXISTS (SELECT 'x'

                  FROM degreethesis x
                  WHERE x.idteacher = a.idteacher
                    AND x.mark = 0
                  GROUP BY x.idteacher
                  HAVING COUNT(idthesis) > 1)
   AND EXISTS (SELECT 'x'
                FROM degreethesis y
                WHERE y.idteacher = a.idteacher
                 AND y.year = a.year
                GROUP BY year, idteacher
                HAVING MAX(mark) = a.mark)

HTH, Dan

"Clairwil" <clairwil_at_hotmail.com> wrote in message news:3C12a.104860$ZE.2948691_at_twister2.libero.it...
> I'd like to know if somebody can tell me how to do this:
>
> I have such a db:
>
> Student (IdStudent, Name, Surname, Address)
> DegreeThesis (IdThesis, ­Tìtle, IdTeacher, IdStudent, Mark, Year)
> Teacher (IdTeacher, Surname)
>
> What I'm asked to find is
>
> all the IdThesis that have got the best mark for each year for each
teacher
> who's now linked to (at least) two theses not yet evaluated (it means mark
> =0).
>
> I can find rightly all the teacher and all best marks, but I can't link
them
> correctly to their theses: it selects IdThesis related to a correct
teacher
> also if the mark is not the best of that year, but it is present in the sq
> Mark IN.
>
> This is my SQL code:
>
> SELECT IdThesis FROM
> (SELECT IdThesis, IdTeacher, Mark, Year FROM DegreeThesis
> WHERE IdTeacher IN
> (SELECT IdTeacher FROM
> (SELECT IdTeacher , COUNT(*) FROM DegreeThesis WHERE Mark= 0
> GROUP BY IdTeacher HAVING COUNT(*)>=2)
> )
> AND Mark IN
> (SELECT MaxMark FROM (SELECT IdTeacher , Max(Mark) AS MaxMark
,Year
> FROM DegreeThesis WHERE Mark<> 0 GROUP BY IdTeacher , Year )
> );
>
> You've been so great in past..I hope you're great again...
>
> Many thanks
>
> A newbie
>
>
>
>
>
>
>
Received on Tue Feb 11 2003 - 23:18:52 CET