How to generate unique number for unique values in MySQL?

Question

I get stuck in creating unique numbers for unique values. I have 25 rows in MySQL table I want to assign rank on it. Suppose 10 users got same values: I want to assign them same number and the rest of them would be unique. I have following MySQL query:

(SELECT std_login_id AS ID, STD_NAME, TOTAL_MARKS, (@rnk := @rnk + 1) AS RANK FROM 
(SELECT ob.std_login_id, SUM(ob.marks_obtain) AS TOTAL_MARKS, std.student_name AS STD_NAME
    FROM exam_marks_obt ob
    JOIN ac_std_preadmission STD ON ob.std_login_id = std.std_login_id
    JOIN adm_class AS cl ON cl.class_id = std.class_id
    JOIN adm_section se ON se.section_id=std.section_id
    WHERE cl.class_id = 1 AND se.section_id = 1
    GROUP BY ob.std_login_id
    ORDER BY ABS(SUM(ob.marks_obtain)) DESC)
ob CROSS JOIN (SELECT @rnk := 0) AS flyRank )
;

Following are the result:

ID | STD_NAME | TOTAL_MARKS | RANK
1  | name1    | 250         | 1
2  | name2    | 250         | 2
3  | name3    | 200         | 3
4  | name4    | 200         | 4
5  | name5    | 150         | 5
6  | name6    | 150         | 6

I want to generate Rank Like this for 250=>1, and 200=>2, and 150=>3. Please tell me what can I do in my code to generate this rank?

barry-johnson · Accepted Answer · 2014-03-02 14:23:23Z

I created a test table to simulate your inner selection that aggregates the marks. Then, allowing for a switch in column order, I tried to simply employ your approach since you are already using variables in your query.

SELECT std_login_id AS ID, STD_NAME,
       (@rnk := IF(@track = TOTAL_MARKS , @rnk, @rnk + 1)) AS RANK,
       (@track:= TOTAL_MARKS) as TOTAL_MARKS
  FROM ( select *
           from testtable
       ORDER BY total_marks DESC) ob
CROSS JOIN (SELECT @rnk := 0, @track := 0) AS starter;

This will produce what you want, I believe. There is probably a better way, but it is late, so I'm going with it. :-)

If you want to test it quickly, just paste and go:

create table testtable (
    std_login_id int(11) primary key auto_increment,
    total_marks int(11),
    std_name  varchar(10)
);

insert into testtable (total_marks,std_name) values (10,'name1');
insert into testtable (total_marks,std_name) values (15,'name2');
insert into testtable (total_marks,std_name) values (15,'name3');
insert into testtable (total_marks,std_name) values (40,'name4');
insert into testtable (total_marks,std_name) values (50,'name5');
insert into testtable (total_marks,std_name) values (15,'name6');
insert into testtable (total_marks,std_name) values (10,'name7');
insert into testtable (total_marks,std_name) values (20,'name8');
insert into testtable (total_marks,std_name) values (10,'name9');
insert into testtable (total_marks,std_name) values (10,'name10');
insert into testtable (total_marks,std_name) values (50,'name11');
insert into testtable (total_marks,std_name) values (10,'name12');
insert into testtable (total_marks,std_name) values (25,'name13');
insert into testtable (total_marks,std_name) values (10,'name14');
insert into testtable (total_marks,std_name) values (50,'name15');
insert into testtable (total_marks,std_name) values (10,'name16');
insert into testtable (total_marks,std_name) values (50,'name17');
insert into testtable (total_marks,std_name) values (20,'name18');
insert into testtable (total_marks,std_name) values (40,'name19');
insert into testtable (total_marks,std_name) values (30,'name20');

Hi barry johnson, You made my day. You code is working like charm. thanks buddy. Great idea with IF i totally forgot about it. Thanks for your effort to resolve my problem.

Saeed · Accepted Answer · 2014-03-02 08:35:38Z

1

You can use this query, which results total marks with their right ranks.

(SELECT TOTAL_MARKS, (@rnk := @rnk + 1) AS RANK 
FROM 
(
    (SELECT distinct(TOTAL_MARKS) AS TOTAL_MARKS 
    FROM
        (SELECT SUM(ob.marks_obtain) AS TOTAL_MARKS
        FROM exam_marks_obt ob
        JOIN ac_std_preadmission STD ON ob.std_login_id = std.std_login_id
        JOIN adm_class AS cl ON cl.class_id = std.class_id
        JOIN adm_section se ON se.section_id=std.section_id
        WHERE cl.class_id = 1 AND se.section_id = 1
        GROUP BY ob.std_login_id
        ORDER BY ABS(SUM(ob.marks_obtain)) DESC)) ob 
    CROSS JOIN (SELECT @rnk := 0) AS flyRank ))

Something like this:

| TOTAL_MARKS | RANK
| 250         | 1
| 200         | 2
| 150         | 3

Now you can join this with a table of STD_NAMEs and their TOTAL_MARKs.

edited Mar 2, 2014 at 8:35

answered Mar 2, 2014 at 7:19

Saeed

7,39014 gold badges47 silver badges66 bronze badges

1 Comment

halfer Over a year ago

@user3370108: if something does not work for you, it is helpful for the poster if you can explain what is not working. I've not tried this, but it looks pretty good - so perhaps you could use it as hints for what to try next. Frequently, answers should be used as starting points rather than something that can be typed in verbatim.

Collectives™ on Stack Overflow

How to generate unique number for unique values in MySQL?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related