1

I have a Student database having one table EXAM.

id    1  2  3  4  5
marks 7 10  7 10  9       

Question is how to give rank like

+------+
| rank |
+------+
|    3 |
|    1 |
|    3 |
|    1 |
|    2 |
+------+

I have used a Query:-

SELECT id, marks, FIND_IN_SET( marks, (
SELECT GROUP_CONCAT( marks
ORDER BY marks DESC ) 
FROM exam )
) AS rank
FROM exam;

but it gives rank

+------+
| rank |
+------+
|    4 |
|    1 |
|    4 |
|    1 |
|    3 |
+------+

What should I do??

2

Just add the word DISTINCT in front of the first marks inside the GROUP_CONCAT function

SELECT id, marks, FIND_IN_SET( marks, (
SELECT GROUP_CONCAT( DISTINCT marks
ORDER BY marks DESC ) 
FROM exam )
) AS rank
FROM exam;

First here is the sample data

use test
DROP TABLE IF EXISTS exam;
CREATE TABLE exam
(
    id int not null auto_increment,
    marks int not null,
    primary key (id)
);
INSERT INTO exam (marks)
VALUES (7),(10),(7),(10),(9);
SELECT * FROM exam;

Let's load the sample data

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS exam;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> CREATE TABLE exam
    -> (
    ->     id int not null auto_increment,
    ->     marks int not null,
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO exam (marks)
    -> VALUES (7),(10),(7),(10),(9);
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM exam;
+----+-------+
| id | marks |
+----+-------+
|  1 |     7 |
|  2 |    10 |
|  3 |     7 |
|  4 |    10 |
|  5 |     9 |
+----+-------+
5 rows in set (0.02 sec)

Now, watch the output of the proposed query:

mysql> SELECT id, marks, FIND_IN_SET( marks, (
    -> SELECT GROUP_CONCAT( DISTINCT marks
    -> ORDER BY marks DESC )
    -> FROM exam )
    -> ) AS rank
    -> FROM exam;
+----+-------+------+
| id | marks | rank |
+----+-------+------+
|  1 |     7 |    3 |
|  2 |    10 |    1 |
|  3 |     7 |    3 |
|  4 |    10 |    1 |
|  5 |     9 |    2 |
+----+-------+------+
5 rows in set (0.00 sec)

mysql>

Give it a Try !!!

| improve this answer | |
  • yes i tried and it worked....thank you so much... – user21931 Mar 30 '13 at 18:25

Not the answer you're looking for? Browse other questions tagged or ask your own question.