MySql: Find row number of specific record

Question

I am working with a generic data panel that can have a variety of different queries feeding that panel. They might be simple queries that select from a table or view, or complex ones that users define themselves with complicated joins and other expressions. I am trying to modify my data panel so that if a user selects a record, then sorts the table, I find the page that record is now on, move to it, and then reselect the record.

I've got most of this worked out, except I am having trouble finding the page number to move to. I originally simply looped over the rows in the data panel but because of paging, this proved to be very inefficient the larger the page number became. I instead decided to do this directly through SQL which is where I am stuck now.

I decided that if I could find the row num of the selected row by running the same query that produced the results I could calculate the final page number that I need to move to and jump right to that page. I took the query that was run to generate the results, and incremented a variable to get the row numbers.

The original query was

select *
from table_a
order by column_c desc

And the modified query with row numbers became

select *, (@rownum := @rownum + 1) as rownum
from
(select @rownum := 0) rn
, (
  select *
  from table_a
  order by column_c desc
) data

At this point I'm selecting all records. I then wrapped the above query and selected the minimum row where the record matched my selected record like so

select min(rownum)
from
(  
  select *, (@rownum := @rownum + 1) as rownum
  from
  (select @rownum := 0) rn
  , (
    select *
    from table_a
    order by column_c desc
  ) data
) wrapper
where
  primarykeyfield1 = ?
  and primarykeyfield2 = ?

At first this appeared to be working. During testing however, I discovered that if I sorted on a field that was not sufficiently unique (e.g. 1000 records all had the same value in this field) it stopped working. I did some digging and found that the above code would return a different rownum every time the query was run.

After some additional digging, I found that if were to run the following query, I would get the results I wanted

select * from table_a order by column_c

But if I simply wrapped that query like so

select * from (select * from table_a order by column_c)

The order of the records changed drastically every time I ran the query. This explains why the row num was changing because it is in fact changing. However, I can't figure out why the order would change simply by wrapping the query. I've done this in other database engines so I assume it has to do with MySql specifically but I have been unable to locate information to explain why. My presumption is that the order by is either not applied when wrapped in query like this, or the behavior is not as expected.

Next, I attempted to move the rownum count directly into the main/base query like so

select *, (@rownum := @rownum + 1) as rownum
from (select @rownum := 0) rn, table_a
order by column_c desc

Running this query by itself creates the correct row numbers. However, since I need to find the specific row num of the selected record I must wrap that query like so

select min(rownum)
from (
  select *, (@rownum := @rownum + 1) as rownum
  from (select @rownum := 0) rn, table_a
  order by column_c desc
) data
where
  primarykeyfield1 = ?
  and primarykeyfield2 = ?

As soon as I do this, the order by seems to be ignored and it counts things in the order that the records appear in the table, instead of the order they are in in the base query.

I'm interested in both understanding the underlying issue with the data set sort not being applied correctly when wrapped up, as well as potential other solutions for finding the page number that a specifc record resides on.

Note, I am using min on the final outer query because ultimately the user may select multiple rows, and all of those rows go into the final where clause. So I want to find the lowest row num and move to that page.

Consider simplifying your problem. No one wants to read through all that! — Strawberry, Jun 07 '13 at 18:16
Please also include your database schema and preferably some example data. — Bad Wolf, Jun 07 '13 at 18:28

score 5 · Answer 1 · answered May 22 '14 at 06:32

My purpose is solved:) So, I am posting here if anyone finds it suitable:

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 which may be used to determine the offset value for pagination, 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 and so on.....

Mike Brant · Answer 2 · 2013-06-07T18:52:16.413

That is a lot to take in, so let me try to break down the problem. First it seems the you need to resolve the problem of unpredictable row ordering.

My suggestion for you here is that you always sort on the unique primary key of the table in some fashion. If the user is viewing the page in it's original "unsorted" (by user selection that is) order, still use a sort like:

SELECT *
FROM table
ORDER BY primary_key ASC
LIMIT 0, [page limit value]

When the user selects to sort by some other field, make sure the primary key field is still used in the sort like this:

SELECT *
FROM table
ORDER BY sort_field [ASC|DESC], primary_key ASC
LIMIT 0, [page limit value]

This will guarantee you the same order upon multiple calls to the same sort even when the cardinality of the field being sorted by the user is low (i.e. not many distinct values) as you always have the unique primary key field to specify order when the values in the sorted field are equal.

Now on to the problem of being able to go directly to a selected row and page with a change in sort. I am assuming you know the primary key for the selected row and have the ability to use this in the query.

You can first determine this row's offset in the new sort as follows:

SELECT count(*)
FROM table
WHERE
  sort_field <= (SELECT sort_field FROM table WHERE primary_key = ?)
  AND primary_key < ?

That tells you the number of rows (including the selected row) in the data set before your selected row (your offset). Note this is for an ascending sort. You would obviously need to use >= and > for the case of a descending sort.

You then use this offset in the previously shown query, like this:

SELECT *
FROM table
ORDER BY sort_field [ASC|DECS], primary_key ASC
LIMIT [offset], [page limit value]

If you want the actual page number that the row is on (for example if you want the concept of a fixed page (where your selected row may or may not be the first row shown), you can also easily calculate it by dividing the offset by the page limit value and rounding the result. You would then multiple the "offset page" by the "page limit" to determine the offset value to be used.

So say you row is at overall offset of 125 and page limit is 50. Here is pseudo-code

selected_row_offset = 125;
page_limit = 50;
offset_page = floor(selected_row_offset/page_limit);
query_offset = offset_page * page_limit;

And your query would be

SELECT *
FROM table
ORDER BY sort_field [ASC|DECS], primary_key ASC
LIMIT [query_offset], [page limit value]

Obviously for performance reasons, you would need to make sure that all fields for which a sort would be allowed should be indexed.

Kanad Godse · Answer 3 · 2014-05-30T14:06:29.833

Thanks Reza Mamun, I have devised a way to put record numbers such that even if you get data using limit, you will come to know exactly which number the record corresponds to.

SELECT @rownum:=@rownum + 1 AS myRowSerial,
*
FROM myTable, (SELECT @rownum:=0) AS nothingButSetInitialValue
LIMIT 0,10

This would give you the record numbers. Now to implement limit, pass in the same limit for example LIMIT 10,10, then set the rownum to the start of the limit i.e. 10 in the FROM clause

The modified query for limit would then be:

SELECT @rownum:=@rownum + 1 AS myRowSerial,
*
FROM myTable, (SELECT @rownum:=10) AS nothingButSetInitialValue
LIMIT 10,10

Then for the next set of records, it would be:

SELECT @rownum:=@rownum + 1 AS myRowSerial,
*
FROM myTable, (SELECT @rownum:=20) AS nothingButSetInitialValue
LIMIT 20,10

Thanks again, this really saved my day!! :)

**UPDATE:**This will NOT work if you have GROUP BY clause in your query.

MySql: Find row number of specific record

3 Answers3

Linked