SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question how do I rank students in a given class by highest mark attained?

    hi there, I have a query that works perfectly well if executed for just one combined group, but problems begin when I have four classes (W, X, Y, Z) and am trying to rank students in each class. Instead of starting with position 1 its starting with position 4, followed by 8, then 12, etc. When I display for the next class I get ranking with decimal places. Is there any alternative querry I could use to solve this problem or is there anything I'm missing on he particular code below?


    SELECT v1.*, COUNT(v2.averagef1) AS Rank
    FROM f_teachers, summary1 v1
    LEFT JOIN summary1 v2 ON v1.averagef1 < v2.averagef1 OR (v1.averagef1=v2.averagef1 and v1.ssurname = v2.ssurname)
    WHERE f_teachers.year = v1.yr AND f_teachers.class= v1.clas AND f_teachers.class= v2.clas AND t_id=%s
    GROUP BY v1.ssurname, v1.averagef1
    ORDER BY v1.ssurname ASC;

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    several question...

    is summary1 a table or a view?

    what is the purpose of having the f_teachers table in the query?

    are you sure that you will never have more than one student across all your classes with the same surname?

    are you displaying the results using an application language like php? (because if so, that's where you should be doing the ranking)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    summary1 is a table with all the students in different classes,
    f_teachers is a table containing form teachers for all the classes

    when the form teacher logs in, his class list will be displayed and yes there may be students with same surnames in other classes, but what uniquely identifies and matches each form teacher and his students is the class.

    yes, I'm displaying the results using php

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by fatso84 View Post
    yes, I'm displaying the results using php
    then you should use a simple ORDER BY in the query (no grouping and counting, no self-join) and apply the ranking numbers as you print out the results

    much more efficient, not to mention ~way~ simpler

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks its working when it comes to arranging them by order, now the problem comes when I want to write a code which displays the student's position, how do I do this?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by fatso84 View Post
    how do I do this?
    use php to count the rows until you get to that student

    for more details, i suggest you post a new thread in the php forum (don't forget to show them your new query, too)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •