How to get MySQL offset as a column with alias?

Question

What I need is a way to get the current offset as a column in my sql query.

SELECT *, <THE_CURRENT_QUERY_OFFSET> AS current_query_offset
  FROM test_table
 LIMIT 10 OFFSET 25

So, I should have a column named current_query_offset which is 25 in each row.

Note: I don't need the current row position the way it is given in With MySQL, how can I generate a column containing the record index in a table? .

When you say `LIMIT/OFFSET` i assume, you must put order in you data. And what is meaning of `LIMIT 0`? — Hamlet Hakobyan, Jan 17 '13 at 13:23
Its better you keep the offset in a variable and use it in your query — Neeraj, Jan 17 '13 at 13:49

score 1 · Answer 1 · answered Jan 17 '13 at 13:20

1

How about this:

SELECT *, 25 AS current_query_offset
FROM test_table
LIMIT 0 OFFSET current_query_offset

answered Jan 17 '13 at 13:20

cowls

23,287
8
47
77

1

did you really test this? I think not because you cannot do this. – John Woo Jan 17 '13 at 13:26

score 1 · Answer 2 · answered Jan 17 '13 at 15:48

If I understand correctly what you want to achieve (that is: selecting a named column containing a constant value along with any other columns), this will work:

SELECT *, 25 AS current_query_offset
FROM test_table
LIMIT 10 
OFFSET 25;

Since you're supplying the value 25 to your sql code once already, I don't see why you shouldn't do it a second time.

How to get MySQL offset as a column with alias?

2 Answers2