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!
2008/1/26, K. R. [email protected]:
5 silvester → that’s position 2
19 michael → that’s position 3
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
2008/1/26, K. R. [email protected]:
19 michael → that’s position 3
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.
–
life, n.:
A whim of several billion cells to be you for a while.
On 26/01/2008, Stefan L. [email protected] wrote:
select count(*) from records where id < 42;
select count(*) from records where id <= 42;
-Thomas
–
Thomas P.
[email protected]
[email protected]
Büro: 030 - 830 353 88
mobil: 0176 - 75 03 03 04
Privat: 030 - 49 78 37 06