CODEWITHSUNDEEP
mysqlsqlalchemy
Vigrond
Vigrond

Reputation: 8198

Row number of a record in a query

I need to find the page number of a record in a query. I have offset and limit, but will also need the row number to calculate this.

Given a query object, and a record ID, how could I find the row number?

row number should be relative to the query table. limit and offset will be applied afterwards.

Any help appreciated...

Upvotes: 1

Views: 4000

Answers (3)

Reza Mamun
Reza Mamun

Reputation: 6189

After a long searching, I found the following MySQL solution:

SELECT d.myRowSerial
FROM (
    SELECT *, @rownum:=@rownum + 1 AS myRowSerial 
    FROM myTable, (SELECT @rownum:=0) AS nothingButSetInitialValue 
    WHERE 1=1 -- Optional: filter if required, otherwise, omit this line;
    ORDER BY AnyColumn -- Apply the order you like; 
) d
WHERE d.myColumn = 'Anything'; -- If you like to limit it to only
-- for any specific row(s), similar to the *MAIN query.

If you need the page number too, then just change the 1st line above like this:

SELECT d.myRowSerial, FLOOR((d.myRowSerial-1)/10) AS pageNumber
-- Say, 10 is per page;

You will have pageNumber==0 for page 1, and pageNumber=1 for page 2, .....

Upvotes: 0

Vigrond
Vigrond

Reputation: 8198

I've looked long and hard, and after 6hrs found a solution.

I use Mysql so I believe the only way I can do this is with @ variables.

Here is how I implemented it into SQLAlchemy:

qry = ...
sel = select([qry.c.id, "@rownum := @rownum + 1 as rownumber"])
conn = sel.engine.connect()
conn.execute("SET @rownum:=0")
res = conn.execute(sel)

@rownum will increment with each row. we can add more columns from qry on the 2nd line if we wanted to. res is the final table with the rownumber column, where we can call res.fetchall() to return a list

Upvotes: 0

zzzeek
zzzeek

Reputation: 75277

It's a little awkward to get the "page number" of the record going backwards like that, usually a system that wants to bounce from the "detail" of a record back to the "paginated view of all of them" would just carry along the page number to the "detail" page.

But assuming you're working with a capable database you can get row numbers out of an arbitrary SELECT statement using a window function, which you can get with a DB like Postgresql, SQL Server or Oracle (notably not MySQL or SQLite).

Using PG we can start with some data:

test=> create table data(id SERIAL primary key, value varchar(20));
NOTICE:  CREATE TABLE will create implicit sequence "data_id_seq" for serial column "data.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "data_pkey" for table "data"
CREATE TABLE
test=> insert into data (value) values ('d1'), ('d2'), ('d3'), ('d4'), ('d5'), ('d6'), ('d7'), ('d8'), ('d9'), ('d10'), ('d11'), ('d12'), ('d13'), ('d14'), ('d15'), ('d16');
INSERT 0 16

we can then select this data and get an integer row count using the row_number() window function: 

test=> select value, row_number() over (order by id) as rownum from data;
 value | rownum 
-------+--------
 d1    |      1
 d2    |      2
 d3    |      3
 d4    |      4
 d5    |      5
 d6    |      6
 d7    |      7
 d8    |      8
 d9    |      9
 d10   |     10
 d11   |     11
 d12   |     12
 d13   |     13
 d14   |     14
 d15   |     15
 d16   |     16
(16 rows)

Applying the windowed data inside of a subquery, we can select slices of the result based on this count:

test=> select value from (select value, row_number() 
     > over (order by id) as rownum from data) as sub where rownum between 5 and 10;
 value 
-------
 d5
 d6
 d7
 d8
 d9
 d10
(6 rows)

So if you had the record "d14" and a page size of 5 you could do this:

test=> select (rownum - 1) / 5 from (select value, row_number() 
     > over (order by id) as rownum from data) as sub where value='d14';
 ?column? 
----------
        2
(1 row)

SQLAlchemy provides window functions via the over() method/function, so a SQLA query assuming a typical ORM mapping for this would look like:

subq = session.query(
            Data.value, 
            func.row_number().over(order_by=Data.id).label('rownum')
       ).subquery()
pagenum = session.query((subq.c.rownum - 1) / 5).\
       filter(subq.c.value == 'd14').scalar()

As far as using window functions vs. limit/offset for pagination, it's worth checking out the comparison I wrote over here comparing the performance of the various methods, as well as the windowed range query recipe I sometimes use.

Upvotes: 2

Related Questions