How to get row position in MySQL?

Question

First of all, I've tried to google this, but only came up with this kind of answer: MySQL - Get row number on select

What I need is, to "get row position by speciefic id".

With the example from above link, I tried to add the "WHERE CLAUSE", but, the result of "rank" was always 1.

So, if my table has auto increment id field, and is not in specific order (for example, after id 1 comes 3, not 2, because id 2 was deleted from database), how can I get the position of for example id 4?

Is there any possible way to get, what I want?

Just wondering, what would you ever need that information for? — Pitchinnate, May 16 '13 at 17:54
I don't fully understand the question. You want to sort the results? Or get certain rows by their ID? Code sample would help. — , May 16 '13 at 17:55
@JosiahSouth No, I don't want to sort results. I need to know the position of row with specific id in table. For example, I have tv_channels table. In that table I have 3 rows. Each row has 2 fields: id and name. Id of first row is 1 and name is BBC. Id of second row is 3 and name is Discovery. Id of third row is 4 and name is MTV. How can I get the position of second row's, of channel Discovert. This position should be number 2. — Lado Lomidze, May 16 '13 at 18:00
As @EvanMulawski said (which is why I was confused) there is no concept of positioning in SQL. I would suggest reading up on SQL. No offense intended. — , May 16 '13 at 18:01
So, what other options do I have? I think, I will add an extra field to table, "position" maybe, and will add values by hand. Can you think of any simpler way ? — Lado Lomidze, May 16 '13 at 18:03
@LadoLomidze: What is wrong with [my answer](http://stackoverflow.com/a/16594272)? — eggyal, May 16 '13 at 18:04
@eggyal Actually, just tested it and nothing. It's exactly what I needed. Thank you! — Lado Lomidze, May 16 '13 at 18:06

score 5 · Answer 1 · answered May 16 '13 at 17:55

5

Assuming that, by "position", you mean when ordered by id:

SELECT COUNT(*) FROM my_table WHERE id <= 4

answered May 16 '13 at 17:55

eggyal

122,705
18
212
237

@eggyal Excellent – Hamed M Feb 26 '19 at 12:09

score 5 · Answer 2 · edited Jul 15 '14 at 14:44

5

For 1-based position:

SELECT COUNT(id) AS pos FROM table WHERE id <= current_id

For 0-based, change <= for <

edited Jul 15 '14 at 14:44

Josef Sábl

7,538
9
54
66

answered May 16 '13 at 17:55

Henrique Barcelos

7,670
1
41
66

For 0-based, change `<=` for `<` – Henrique Barcelos May 16 '13 at 17:56
Can you add your comment to your answer? – Ryan Gates May 16 '13 at 18:24
You can do this, but adding it to your answer would improve your answer. – Ryan Gates May 16 '13 at 19:30
You're right... I'm new here, just don't know how to use all features yet... Thank's... – Henrique Barcelos May 16 '13 at 19:37
No problem. You can always edit your own questions and answers. You can go [here to learn about other privileges](http://stackoverflow.com/privileges/). – Ryan Gates May 16 '13 at 19:45
This is actually great idea, thanks. For multiple orders (ORDER BY id ASC, value DESC) see my answer below. – Josef Sábl Jul 15 '14 at 14:45
I like it... Find the ID of your desired record, and count the rows before it. – alfadog67 Jul 01 '16 at 14:47

Deval Shah · Answer 3 · 2013-05-16T19:33:42.007

Look at these SQL Fiddle.

CREATE TABLE league_girl (position int, username varchar(10), score int);
INSERT INTO league_girl VALUES (1, 'a', 10);
INSERT INTO league_girl VALUES (2, 'b', 25);
INSERT INTO league_girl VALUES (7, 'c', 75);
INSERT INTO league_girl VALUES (10, 'd', 25);
INSERT INTO league_girl VALUES (16, 'e', 55);
INSERT INTO league_girl VALUES (17, 'f', 80);
INSERT INTO league_girl VALUES (27, 'g', 15);

And This query gives me record where position = 17 and its 6th Row Number

Select * from (SELECT  l.position, 
        l.username, 
        l.score,
        @curRow := @curRow + 1 AS row_number
FROM    league_girl l
JOIN    (SELECT @curRow := 0) r) as k
WHERE   k.position = 17;

score 0 · Answer 4 · answered May 16 '13 at 17:59

0

MySQL does not have any function for finding row number if your mean

answered May 16 '13 at 17:59

Amir

4,089
4
16
28

score 0 · Answer 5 · answered Jul 15 '14 at 15:02

I needed something quite similar but more general. I have select query with several order clauses and I needed to find a position of particular id in the result set. Imagine a filtered list of products and set of filters and what you need is to find which page the product is on.

Based on Henrique Barcelos solution:

Original query:

SELECT * FROM mytable 
WHERE something = TRUE
ORDER BY 
    price ASC, 
    discount DESC, 
    catagory ASC

Find row query for specific id:

SELECT COUNT(id) FROM mytable
WHERE something = TRUE
AND (
    price < 'myprice'
    OR (price = myprice AND discount > 'mydiscount')
    OR (price = myprice AND discount = 'mydiscount' AND category < 'mycategory')
)

You have to get the myprice, mydiscount and mycategory of the object you need to get the position of first.

You can use less than (<) for ascending order and greater than (>) for descending order.

This query can be easily generated algorithmically.

It is also much more resource effective than the solution with variable @curRow as it does not need to fetch rows and can compute the result from index only.

luky · Answer 6 · 2016-11-05T20:19:00.197

0

SET @curRow = 0;
SELECT *, @curRow := @curRow + 1 row FROM `table`

if you want to store the real order as value in the table, you can add new column eg row and then

set @curRow = 0;
update `table` set row = @curRow := @curRow + 1;

or you can update the primary key if you want to clear the gaps (if it is save to change the ids, eg in case it is just solo testing table)

edited Nov 05 '16 at 20:19

answered Nov 05 '16 at 20:06

luky

2,263
3
22
40

How to get row position in MySQL?

6 Answers6