How to find rows in SQL / MySQL with ORDER BY

Question

I have a table user

Name     | Poin 
================== 
user1    | 20 
user2    | 30 
user3    | 80 
user4    | 60 
user5    | 10 
user6    | 85

And I have SQL query

SELECT * 
FROM user 
ORDER BY poin

It would appear that the data sequence based on points.

But what I need is data like this (for example, I was user1):

Position 1 : user6 - 85 point 
Position 2 : user3 - 80 point 
Position 3 : user4 - 60 point

You are position 5 : user1 - 20 point

UPDATE

I use this sql

SELECT x.name, x.position
FROM (SELECT t.user,
            @rownum := @rownum + 1 AS position
            FROM user t
           JOIN (SELECT @rownum := 0) r
           ORDER BY t.poin DESC) x
WHERE x.user = 'user1'

mvp · Accepted Answer · 2013-02-05 11:43:15Z

1

This will give current rank for user1:

SELECT count(*) AS rank
FROM user
WHERE poin >= (SELECT poin FROM user WHERE name = 'user1')

Small issue with this query is that if another user has the same points, it will be assigned the same rank - whether it is correct, it is questionable.

If you want to simply add rank for every user, use this:

SELECT
    @rank:=@rank+1 AS rank,
    name,
    poin
FROM user,
    (SELECT @rank:=0) r
ORDER BY poin DESC

You can use small variation of this query to get rank of single user, but avoid issue of the same ranking ambiguity:

SELECT *
FROM (
    SELECT
        @rank:=@rank+1 AS rank,
        name,
        poin
    FROM user,
        (SELECT @rank:=0) r
    ORDER BY poin DESC
) x
WHERE name = 'user1'

edited Feb 5, 2013 at 11:43

answered Feb 5, 2013 at 10:31

mvp

118k15 gold badges132 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

a.fauzi Over a year ago

thx for response @mvp.. I use the first choice of you. but there was a slight problem when there is a point value equal to User1. how do i fix this .. thx before.. :)

mvp Over a year ago

I have updated my answer to give an option to disable "ties" - all users will have unique rankings, even if they have the same score

a.fauzi Over a year ago

GREAT.. this work. why did not I think to do multiple queries like that :p .... ok thank you so much @mvp .. Your partner helps .. ;)

Roger · Accepted Answer · 2013-02-05 10:28:15Z

0

select * from user order by poin desc

Hope this helps

answered Feb 5, 2013 at 10:28

Roger

1,7011 gold badge18 silver badges34 bronze badges

1 Comment

Roger Over a year ago

@johannes: is that so? Your code showed you knew about queries and all. So dint made an explanation

William Kinaan · Accepted Answer · 2013-02-05 10:28:29Z

0

SELECT * FROM user ORDER BY poin DESC

The ORDER BY keyword is used to sort the result-set by a specified column.

The ORDER BY keyword sorts the records in ascending order by default.

If you want to sort the records in a descending order, you can use the DESC keyword.

SQL ORDER BY Syntax

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC

answered Feb 5, 2013 at 10:28

William Kinaan

29k20 gold badges89 silver badges122 bronze badges

3 Comments

Bhavik Shah Over a year ago

fauzi wants the position and not description of 'order by'. I really dont understand how this answer helps user.

William Kinaan Over a year ago

I understand the question is the he wants to order the users, I think he used position just for example

mvp Over a year ago

If I understand correctly, OP needs user current position (rank). Your query simply does not have it

paul · Accepted Answer · 2013-02-05 10:31:40Z

0

SELECT  Name, 
        Poin, 
        @rowNum := @rowNum + 1 AS position
FROM    user
JOIN    (SELECT @rowNum := 0) r
ORDER BY poin;

answered Feb 5, 2013 at 10:31

paul

22.1k1 gold badge56 silver badges54 bronze badges

1 Comment

a.fauzi Over a year ago

thank you for the solution, but I can not use it because it has to process all data to rank

cmh · Accepted Answer · 2013-02-05 11:47:01Z

0

The following code:

SELECT count(*) AS rank 
FROM user 
WHERE poin >= (SELECT poin FROM user  WHERE name = 'user1')

Has a problem when is has double point in different rows.

edited Feb 5, 2013 at 11:47

cmh

11k5 gold badges33 silver badges40 bronze badges

answered Feb 5, 2013 at 11:22

saepulloh

1

Comments

Raunak · Accepted Answer · 2013-02-05 10:41:54Z

-1

SELECT  Name, 
        Poin, 
        @rowNum := @rowNum + 1 AS position
FROM    user
JOIN    (SELECT @rowNum := 0) r
ORDER BY poin DESC;

edited Feb 5, 2013 at 10:41

answered Feb 5, 2013 at 10:28

Raunak

171 silver badge7 bronze badges

2 Comments

a.fauzi Over a year ago

thank you for the solution, but I can not use it because it has to process all data to rank

Raunak Over a year ago

@a.fauzi: Glad to hear that. If this is the answer that helped you and brought you wonders then just click on the "right/tick" in the upper left side down to "-1" scroll-bar so that other users will find it in short span of time if they'd look for exactly or relevant answer.

Collectives™ on Stack Overflow

How to find rows in SQL / MySQL with ORDER BY

UPDATE

6 Answers 6

3 Comments

1 Comment

SQL ORDER BY Syntax

3 Comments

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

UPDATE

6 Answers 6

3 Comments

1 Comment

SQL ORDER BY Syntax

3 Comments

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related