Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2010
    Posts
    3

    Unanswered: How to find position in Mysql

    Hi, Guys: I have a table in MySql database which contains marks of students and their classes. Eg. (Jonas, 86, B2)(John, 67,B3). I need a query in sql or mysql which will not only arrange the marks in ascending order but also add the positions in class. Eg(Jonas, 86, 1st), (Evans, 85, 2nd) etc. Please I need y'all help.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT name
         , class
         , mark
         , ( SELECT COUNT(*) + 1
               FROM daTable
              WHERE class = t.class
                AND mark > t.mark ) AS rank
      FROM daTable AS t
    ORDER
        BY class
         , mark DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2010
    Posts
    3
    Thank you for your help.When i run the query, the rank is always the same. it doesn't change or decrease as expected.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    or do this in your front end, where you display/report this information
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by saintjab View Post
    When i run the query, the rank is always the same. it doesn't change or decrease as expected.
    then you must be doing something wrong, because what i gave you works just fine --
    Code:
    CREATE TABLE daTable
    ( class VARCHAR(9)
    , name VARCHAR(37)
    , mark TINYINT
    );
    INSERT INTO daTable VALUES
     ('B1','Curly',84) 
    ,('B1','Larry',82)
    ,('B1','Moe'  ,86)
    ,('B2','Tom'  ,81)
    ,('B2','Rick' ,84)
    ,('B2','Hairy',87)
    ,('B2','Jonas',86)
    ,('B3','John' ,67)
    ;
    SELECT name
         , class
         , mark
         , ( SELECT COUNT(*) + 1
               FROM daTable
              WHERE class = t.class
                AND mark > t.mark ) AS rank
      FROM daTable AS t
    ORDER
        BY class
         , mark DESC
    ;
    name   class   mark   rank
    Moe     B1       86      1
    Curly   B1       84      2
    Larry   B1       82      3
    Hairy   B2       87      1
    Jonas   B2       86      2
    Rick    B2       84      3
    Tom     B2       81      4
    John    B3       67      1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Aug 2010
    Posts
    3
    Thanks r937. It is now working. there was a slight mistake somewhere. You have made my day.

Posting Permissions

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