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 created sql fiddle as below
it is working fine if i remove where condition(WHERE rg.groupid= 1)

output without where condition

| ID | RANK | MYORDER | PREVIOUSRANK |
|----|------|---------|--------------|
|  3 |    7 |       1 |            7 |
|  2 |    3 |       2 |            3 |
|  4 |    3 |       2 |            3 |
|  1 |    2 |       3 |            2 |

i want my result order by(ORDER BY mro.rank DESC) but it does not return desired result

SQL Fiddle

CREATE TABLE rankGroup
    (
     id int auto_increment primary key, 
     groupid int
    );

INSERT INTO rankGroup
(groupid)
VALUES
(1),(2),
(1),(1);

CREATE TABLE myRankorder 
    (
     id int auto_increment primary key, 
    groupid int,
     rank int
    );

INSERT INTO myRankorder
(groupid, rank)
VALUES
(1,2),(2,3),
(3,7),(4,3);

Query 1:

SELECT mro.id, mro.rank,
         IF (@prevrank = mro.rank, @rownum, @rownum := @rownum + 1) AS `myorder`,
         @prevrank := mro.rank AS `previousRank`
    FROM `myRankorder` mro
    INNER JOIN `rankGroup` rg
        ON rg.`id` = mro.`groupid`
    cross JOIN
        (SELECT @rownum := 0, @prevrank := 0) const
    WHERE rg.`groupid` = 1  
    ORDER BY mro.rank DESC

Results:

| ID | RANK | MYORDER | PREVIOUSRANK |
|----|------|---------|--------------|
|  3 |    7 |       2 |            7 |
|  4 |    3 |       3 |            3 |
|  1 |    2 |       1 |            2 |

Desired Result

| ID | RANK | MYORDER | PREVIOUSRANK |
|----|------|---------|--------------|
|  3 |    7 |       1 |            7 |
|  4 |    3 |       2 |            3 |
|  1 |    2 |       3 |            2 |
share|improve this question

1 Answer 1

up vote 1 down vote accepted

Try it this way

SELECT id, rank,
       @rownum   := IF(rank = @prevrank, @rownum, @rownum + 1) myorder,
       @prevrank := rank previousRank
  FROM 
(
  SELECT mro.id, mro.rank
    FROM myRankorder mro JOIN rankGroup rg
      ON rg.id = mro.groupid 
   WHERE rg.groupid = 1 
   ORDER BY mro.rank DESC
) q CROSS JOIN
(
  SELECT @rownum := 0, @prevrank := NULL
) const

Output:

| ID | RANK | MYORDER | PREVIOUSRANK |
|----|------|---------|--------------|
|  3 |    7 |       1 |            7 |
|  4 |    3 |       2 |            3 |
|  1 |    2 |       3 |            2 |

Here is SQLFiddle demo

share|improve this answer
    
Thanks working fine, I have also find solution :) –  Pragnesh Chauhan Dec 17 '13 at 11:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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