Get row position in MYSQL query

Question

Possible Duplicate:
ROW_NUMBER() in MySQL

Is this possible? I want to get a bunch of results in a specific order and then find out the position of a row by its id, for example:

1, foo
2, bar
3, foobar

I want to get the position of 1 in Alphabetical order, it would return 2

MYSQL .... Say I had a list of comments for a discussion in a database. I want to find the position of the comment which I specify in that particular discussion. (In the comments database there will be a load of different comments for different discussions) — Jake, Aug 14 '11 at 15:48
See existing answer at: http://stackoverflow.com/questions/1895110/row-number-in-mysql — Michael Hays, Aug 14 '11 at 15:56
I looked at that page but I really don't get SQL so could you please put the relevant MYSQL here for me to use, thanks. — Jake, Aug 14 '11 at 15:58
@Jake Stainer: well, you should try and learn then instead of asking "please give me the codez" — gbn, Aug 14 '11 at 16:26

score 10 · Accepted Answer · edited May 23 '17 at 11:47

10

SELECT id, name, rank FROM 
    (
    SELECT t.id, t.name,
        @rownum := @rownum + 1 AS rank
    FROM TABLE t, (SELECT @rownum := 0) r
    ORDER BY name ASC
    ) `selection` WHERE id=1

Modified from this answer >> ROW_NUMBER() in MySQL

edited May 23 '17 at 11:47

Community

1
1

answered Aug 14 '11 at 15:49

potNPan

747
1
6
18

IME, this is only consistent on InnoDB tables. – OMG Ponies Aug 14 '11 at 15:53
Its too confusing for me, I don't get it – Jake Aug 14 '11 at 15:57
@JakeStainer AFAICT, the subquery part joins your table with a variable `@rownum` then increments `@rownum` for each record. I put the whole thing in a subquery so that you can then get the rank by using the "WHERE id=" part. – potNPan Aug 14 '11 at 16:07
I've been playing around with this. It needs tweaking to actually get it to work in MySQL: – Robin Winslow Aug 14 '11 at 17:02

Robin Winslow · Answer 2 · 2011-08-14T17:17:15.953

3

Working answer (in MySQL):

If you have the following table "names":

+------+--------+
| id   | name   |
+------+--------+
|    1 | foo    |
|    2 | bar    |
|    3 | foobar |
+------+--------+

And you want to know where "foo" ranks alphabetically, then:

SELECT z.rank FROM (
    SELECT t.id, t.name, @rownum := @rownum + 1 AS rank
    FROM names t, (SELECT @rownum := 0) r
    ORDER BY name ASC
) as z WHERE id=1;

Will produce:

+------+
| rank |
+------+
|    2 |
+------+

The changes from @potNpan's solution are the addition of as z and the change from @rownum = @rownum + 1 to @rownum := @rownum + 1. Now it works :)

edited Aug 14 '11 at 17:17

answered Aug 14 '11 at 15:43

Robin Winslow

10,908
8
62
91

Say I want to know the position of the id "2" in this table, then I need it to return 2 (because its alphabetical in this case) thanks – Jake Aug 14 '11 at 15:45
This is the opposite of what the OP asked. You're giving the first record in alphabetical order, but the OP wants the rank of the record with ID 1 (i.e. the first record in ID order). Probably he wants a more general solution anyway to get the alphabetical rank of any item. – Kerrek SB Aug 14 '11 at 15:47
Say I had a list of comments for a discussion in a database. I want to find the position of the comment which I specify in that particular discussion. (In the comments database there will be a load of different comments for different discussions) – Jake Aug 14 '11 at 15:48
Oh I think I see. I'll try to achieve that. – Robin Winslow Aug 14 '11 at 15:51
I'm doing this but am just getting the count and not the position! SELECT `discusComments`.id, (SELECT COUNT(*) FROM `discusComments` WHERE (`discusComments`.`id` <= `discusComments`.`id`) AND (`topicID` = 4)) AS position FROM `discusComments` WHERE `discusComments`.`id` = 68 AND (`topicID` = 4) – Jake Aug 14 '11 at 16:10

Get row position in MYSQL query

2 Answers2

Linked