Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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??

share|improve this question

closed as too localized by Mark Storey-Smith, dezso, Max Vernon, Derek Downey, Nick Chammas Apr 30 '13 at 16:50

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer 1

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 !!!

share|improve this answer
    
yes i tried and it worked....thank you so much... –  user21931 Mar 30 '13 at 18:25
    
Please mark my answer accepted ... –  RolandoMySQLDBA Mar 30 '13 at 23:50

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