SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    HardCoder md_irfan_amu's Avatar
    Join Date
    May 2005
    Location
    Asia
    Posts
    215
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    A query which puzzled me ...

    Please help to find out this, the problem is given below.

    i have a table student which contains two fields the student_ID and student_Marks,
    Now I want to genrate a query for the rank according to their ranks,For Example .
    the tables value is suppose some thing like that,

    student_ID student_Marks
    s01 78
    s02 87
    s03 56
    s04 91
    s05 68

    then I want the result as

    Rank Student_ID student_Marks

    1 s04 91
    2 s02 87
    3 s01 78
    4 s05 68
    5 s03 56

    I think you can understand the problem,

    Thanks in advance for the help,
    Irfan
    Find Tutors Easyway to get connect with your tutor in india

  2. #2
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Many ways, for example subselects (slow)
    Code:
    SELECT
    	*,
    	(SELECT COUNT(DISTINCT marks)
    		FROM student AS prev
    		WHERE prev.marks < curr.marks) + 1 AS rank
    FROM
    	student AS curr
    ORDER BY
    	marks DESC
    or variables (naive)
    Code:
    SET @rank := 0;
    SELECT
    	*,
    	(@rank := @rank + 1) AS rank
    FROM
    	student
    ORDER BY
    	marks DESC

  3. #3
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi,
    this will work if you have 4.1 or upper
    Code:
    select student_ID, n.student_Marks, 
                   (select count(*) 
                    from tablename as t 
                    where t.student_Marks > n.student_Marks) as rank
    from tablename as n 
    order by student_Marks desc
    Chagh

  4. #4
    HardCoder md_irfan_amu's Avatar
    Join Date
    May 2005
    Location
    Asia
    Posts
    215
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your help, It looks fine and I think this is the right way to solve this problem
    Irfan
    Find Tutors Easyway to get connect with your tutor in india


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
  •