Order by ID if two users have same number of credits

:-) I have this script, which find a users position taken from the number of credits. It all works, but i have a little problem. If开发者_开发百科 two users have the same credits, both of them will be on the same position. Can I do, so if there are more users with same credits, then the system need to order by the users ID and out from that give them a position?

This is my code so far:

$sql = "SELECT COUNT(*) + 1 AS `number`
        FROM `users` 
        WHERE `penge` > 
                       (SELECT `penge` FROM `users` 
                        WHERE `facebook_id` = ".$facebook_uid.")";
$query_rang = $this->db->query($sql);

So if i have this:

ID -------- Credits

1  -------- 100

2  -------- 100

3  -------- 120

Then the rank list should be like this:

Number 1 is user with ID 3

Number 2 is user with ID 1

Number 3 is user with ID 2

ORDER BY credits DESC, id ASC. This will sort by credits and break ties with the id.

• UPDATE

I understand now that you want the ranking information for the user, not just to sort the users by credits and ids. This will give you the complete list of users and their rankings:

SELECT @rank:=@rank+1 AS rank, users.id, users.facebook_id FROM users, (SELECT @rank:=0) dummy ORDER BY penge DESC, id ASC

Getting the row number is the tricky bit solved by this blog post: http://jimmod.com/blog/2008/09/displaying-row-number-rownum-in-mysql/

$sql = "SELECT COUNT(*) + 1 AS `number` FROM `users` WHERE `penge` > (SELECT `penge` FROM `users` WHERE `facebook_id` = ".$facebook_uid.") ORDER BY COUNT(*) + 1 desc, users.ID";
$query_rang = $this->db->query($sql);

Later EDIT:

I don't understand why you still have the same results....

I made a quick test. I have created a table:

Test: ID (Integer) and No (Integer)

I have inserted some values:

id  no
1   1
1   1
1   1
2   1
3   1
4   1
4   1
5   1

Now, if I run:

SELECT 
  id, COUNT(*) + 1 AS `number`
FROM
  test
GROUP BY
  id

I get:

id  number
1   4
2   2
3   2
4   3
5   2

But if I add ORDER BY:

SELECT 
  id, COUNT(*) + 1 AS `number`
FROM
  test
GROUP BY
  id
ORDER BY 
  count(*) desc, id

then I get:

id  number
1   4
4   3
2   2
3   2
5   2