How to easily get row number when using LIMIT in MySQL?

Question

Suppose I have a database table with quite a few rows which I want to display to an user. It would make sense to LIMIT the output, and make pages of rows. In MySQL I would do this:

SELECT * FROM myTable ORDER BY myValue LIMIT 120,10

which will show 10 rows starting from row 120. So MySQL must use, internally, some kind of order, and has numbered the rows accordingly. I would like to display the row number with each row. How do I get access to these numbers, using only MySQL? To be clear, I am looking for something like this:

SELECT *,<LIMIT_ROWNO> FROM myTable ORDER BY myValue LIMIT 120,10

I looked online and in the manual, but I cannot find it. I would prefer something simple, without using variables, or functions. Isn't there a predefined expression for this?

I can solve this problem easily in PHP, but it would be more logical to get the row numbers from MySQL.

There is no default order. The result is random or a better term is unrelyable. — juergen d, Aug 08 '17 at 10:13
@juergen d: In my example I haven't specified an `ORDER BY`, because it is not needed. Even if the order is random the question remains valid. — KIKO Software, Aug 08 '17 at 10:15
*So MySQL must use some sorting and has numbered the rows* - no! — juergen d, Aug 08 '17 at 10:16
@juergen d: Yes, otherwise the `LIMIT` could not work. I'll edit the word 'sorting' which might be confusing. — KIKO Software, Aug 08 '17 at 10:17
@juergen d: Using variables, in this way, in expressions won't give me the `LIMIT` row number. — KIKO Software, Aug 08 '17 at 10:21
One thing should be clear: If you do not specify an order then the result of offset is not reliable and you don't get the correct results. You NEED an `order by`. — juergen d, Aug 08 '17 at 10:21
@juergen d: I'll add an `ORDER BY` to make it more explicit. — KIKO Software, Aug 08 '17 at 10:23
Now you have a garanteed order. And the answer is that you cannot access what MySQL is doing internally. You need a variable and do this for yourself — juergen d, Aug 08 '17 at 10:24
set @rownum=0;SELECT *,@rownum:=@rownum+1 as rn FROM myTable ORDER BY myValue LIMIT 120,10; — andreas karimi, Aug 08 '17 at 10:34

score 5 · Accepted Answer · answered Aug 08 '17 at 10:33

5

You can't do it without using variables, e.g.:

SELECT m.*, @rownum := @rownum + 1 as `num`
FROM myTable m, (SELECT @rownum := 120) a 
ORDER BY myValue LIMIT 120,10;

answered Aug 08 '17 at 10:33

Darshan Mehta

30,102
11
68
102

Yes, I can see how this would work. Hiding away the initialization of `rownum` in the same query is a 'nice' touch. In the end, I choose to implement it in PHP. I've already got a complicated query, and this makes it even uglier. Anyway, you confirmed my suspicion that I cannot get the row number more directly from MySQL. – KIKO Software Aug 08 '17 at 10:57

andreas karimi · Answer 2 · 2017-08-08T10:53:53.723

3

set @rownum=120;
SELECT *,@rownum:=@rownum+1 as rn FROM myTable ORDER BY myValue LIMIT 120,10;

edited Aug 08 '17 at 10:53

answered Aug 08 '17 at 10:35

andreas karimi

182
7

Adrian Covaci · Answer 3 · 2021-10-06T08:34:32.313

1

as of final of 2021, why not:

SELECT 
t1.*, 
COUNT(t1.*) OVER (PARTITION BY RowCounter) as TotalRecords
FROM (
    SELECT a, b, c, 1 as RowCounter 
    FROM MyTable
) t1 
LIMIT 120,10

using a subquery with a column marking every row with the same value, will give us the possibility to count all of the same values of the the resulted column with PARTITION BY window function's group

edited Oct 06 '21 at 08:34

answered Oct 06 '21 at 08:20

Adrian Covaci

103
8

Can you add an explanation to your answer? – KIKO Software Oct 06 '21 at 08:24
It seems the [Window Functions](https://mariadb.com/kb/en/window-functions-overview/) were only introduced in MariaDB 10.2. We are using MariaDB 5.5. I cannot test your answer, unless I set up a complete test-environment for the latest version. I don't have time for that. I do however understand what you're trying to do. – KIKO Software Oct 06 '21 at 08:57
I found a [database fiddle](https://www.db-fiddle.com/f/mKTd9StqeZVxm4QZLBjwau/0) to run your query. I don't think it does what I asked for in my question. – KIKO Software Oct 06 '21 at 10:12
Ah sorry, I've answered accordingly with the title lol :))) – Adrian Covaci Oct 06 '21 at 13:33

How to easily get row number when using LIMIT in MySQL?

3 Answers3