Hi @ all How can I find out the number of the position of a record in the database?
i.e:
id firstname --- --------- 2 bryan -> that's position 1 5 silvester -> that's position 2 19 michael -> that's position 3
thanks for helping!
Well, the existence of positions requires the definition of an order. I assume that you want the records ordered by id, from lowest to highest.
Instead of asking "what is the position of record x", we can ask "how many records are smaller than x". Well, that's a simple SQL query:
select count(*) from records where id < 42;
assuming the record whose position we want to know has the id 42.
Or, in Rails speak:
position = Record.count(:conditions => ["id < ?", id_of_object_whose_position_we_want_to_know])
HTH, Stefan
Without reference to, as a starting point, an index or indexing function (e.g. "order by"), position is pretty much indeterminate. It depends on how the free space algorithm of the database works and much else. In fact deleting a record form another table might cause some space management function to be invoked and the position changed. (e.g. b-trees). Even this may be changed if you use a disk mapping technology like LVM.
When we write the database out as you have done above we are linearizing. The structure and 'pointers' of the actual database is not necessarily in any way linear. It may be optimized in any one of a number of ways.
The way you've written it out its "order by id". That is in accord with the default index that Rails uses when it creates a table.
Perhaps the question you're asking is "what is the offset of a record in the table when its ordered by id?". That's meaningful regardless of the database technology and how it allocates the position of record on the disk.
select count(*) from records where id <= 42;
-Thomas
