0

I want to return the row number of a particular row (so how many rows before a given row).

Now the problem is the PRIMARY_KEYs are not sequential, so there are 'gaps' inside them, because sometimes I have to DELETE rows. 

id = 1  
id = 2  
id = 5  
id = 9   
id = 10

So the only option to get the row number is to use a COUNT(*):

SELECT COUNT(*) FROM table WHERE id < selected_row_id;

But for a given page I have to perform this operation multiple times.. so one solution is to use a foreach loop, like:

foreach($foo as $item){
     mysql_query("SELECT COUNT(*) FROM table WHERE id < $item['id']");
     //...
}

But I think it's not optimal...if one have thousands of rows and 80-100 iterations for the above foreach.

Another solution would be to rebuild the entire id column after DELETING a row.. but because foreign constraints / references I think this isn't a good step, too.

So if COUNT(*) in a foreach is not viable, then is there anyone who faced this type of problem, what would be the optimal solution?

Thanks for your time, and sry for my bad english.

Filkor
  • 642
  • 6
  • 18
  • Are you able to give any details about where you'll be using the COUNT data that is generated? Perhaps there is another way around this. – philwilks Mar 05 '12 at 11:15
  • take a look at this other question: [Mysql rank function](http://stackoverflow.com/questions/3333665/mysql-rank-function). I think it is what you need – Paolo Falabella Mar 05 '12 at 11:17
  • why do you need it in a loop?the only practical issue I know is how to get just one. What is this array of id's? What is the size of array, by the way? – Your Common Sense Mar 05 '12 at 11:23
  • This problem is just a "skeleton". I can't describe the whole problem here, because it would be too long. – Filkor Mar 05 '12 at 11:34
  • You have to. As your problem seems to be an exact example of the **[XY problem](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem)** and most likely have a way simpler solution. – Your Common Sense Mar 05 '12 at 11:39
  • By the way, is there a possibility for the rows to be ordered not by id but by some other field - by name or date for example? Or filtered using some complex query? – Your Common Sense Mar 05 '12 at 11:43

4 Answers4

1

I recently had a similar issue where, for a given set of results, I wanted to know the position # of a specific result in that set.

There's an elegant solution which will give you sequential row numbers for a resultset, based on internal incrementation of a variable

See http://craftycodeblog.com/2010/09/13/rownum-simulation-with-mysql

Hope that helps you out

solarise
  • 453
  • 2
  • 3
  • In what sense is it a different issue? Both are trying to find the specific # of a particular row in a set of results – solarise Mar 05 '12 at 11:29
  • The assumption would be that the data being iterated in the for loop were originally sourced from the same table - so it would be more efficient to return those results, originally, along with the row numbers. It could be that the results come from somewhere else though, in which case yes, you'd have to do something else to find their position in the table. – solarise Mar 05 '12 at 11:36
0

Instead of rebuilding the id column after each delete or insert, how about just adding a new column to the table to store the data you need?

philwilks
  • 669
  • 5
  • 16
  • In the relational model, the number of a row should be relatively useless, because the order of rows is supposed to be meaningless. But if the row number is real data, this is the way to handle it. – Mike Sherrill 'Cat Recall' Mar 05 '12 at 11:23
0

I think you want to try this:

SELECT parent.Id, count( DISTINCT (child.Id) ) AS previous
FROM table AS parent, table AS child
WHERE child.Id < parent.Id

I don't think it is efficient from a database perspective, but should be better than iterating from code. Caveat is that you need to build the required php code since I am not proficient at that, but should be easy.

Alexei Eleusis Díaz Vera
  • 114
  • 1
  • 3
-1

There are a couple ways to improve this

  • You can sort $items by ID, then keep track of the number of rows above the last item, and add to this the number of rows between the current item and the last item: $last_id <= id AND id < $item['id']
  • Context switching is expensive. It is better to grab all the IDs in a single query, then process the information in PHP.
Daniel Lubarov
  • 7,796
  • 1
  • 37
  • 56
  • context switching? you are talking of the context switching? – Your Common Sense Mar 05 '12 at 11:26
  • don't you think that it is not a CPU context switching going to be a bottleneck here? – Your Common Sense Mar 05 '12 at 11:31
  • Not if the OP's original query is used, since the storage engine would need to read kilobytes of data from an index file. But with my suggested change, yes, the queries would be cheap and the switching would be a bottleneck on most architectures. – Daniel Lubarov Mar 05 '12 at 11:37
  • what about variable order? say, this table is ordered by id at the moment, but what if there is another ordering? we would have to keep track the position of both? – Your Common Sense Mar 05 '12 at 11:46
  • What are you talking about? Are you saying, what if the SELECT does not return results in primary key order? Or what if the OP adds an ORDER BY other_field to his query? Or what if his `$item` array is not ordered by id? The position of both *what*? – Daniel Lubarov Mar 05 '12 at 11:53
  • any of the above. the issue can be anything. The position for both (or more) possible orderings. Why do you think that only primary key order is possible? – Your Common Sense Mar 05 '12 at 11:56
  • The order of the SELECT doesn't matter, he's counting rows not reading them. And I mentioned that `$items` would need to be sorted by id if it isn't already. – Daniel Lubarov Mar 05 '12 at 12:00
  • the order is essential, as with different order there will be different row numbers – Your Common Sense Mar 05 '12 at 12:01
  • How could order matter in a `SELECT count(*)`? Some set of rows satisfy the `WHERE`, the size of a set has nothing to do with the order in which the entries are stored. – Daniel Lubarov Mar 05 '12 at 12:12