Index of a record in a table

Question

I need to locate the index position of a record in a large database table in order to preset a pager to that item's page. I need an efficient SQL query that can give me this number. Sadly SQL doesn't offer something like:

SELECT INDEX(*) FROM users WHERE userid='123'

Any bright ideas?

EDIT: Lets assume there is an ORDER BY clause appended to this. the point is I do not want to have to load all records to locate the position of a specific one. I am trying to open a pager to the page holding an existing item that had previously been chosen - because i want to provide information about that already chosen item within a context that allows a user to choose a different one.

Answer 1

SQL doesn't guarantee the order of objects in the table unless you use the OrderBy clause. In other words, the index of any particular row may change in subsequent queries. Can you describe what you are trying to accomplish with the pager?

Answer 2

You might be interested in something that simulates the rownum() of Oracle in MySQL... if you are using MySQL of course as it's not specified in the question.

---

Notes:

You'll have to look through all the records of your pages for that to work of course. You don't need to fetch them back to the PHP page from the database server but you'll have to include them in the query. There's no magic trick to determine the position of your row inside a result set other than querying the result set as it might change because of the where conditions, the orders and the groups. It needs to be in context.

Of course, if all your rows are sequential, with incremental ids, none are deleted, and you know the first and last ids; then you could use a count and with simple math get the position without querying everything.... but I doubt that's your case, it never is.

Answer 3

Assuming you are really asking how to page records in SQL Server 2005 onwards, have a look at this code from David Hayden:

(you will need to change Date, Description to be your columns)

CREATE PROCEDURE dbo.ShowUsers
    @PageIndex INT, 
    @PageSize INT 
AS

BEGIN 

  WITH UserEntries AS ( 
  SELECT ROW_NUMBER() OVER (ORDER BY Date DESC) AS Row, Date, Description 
  FROM users)

  SELECT Date, Description
  FROM UserEntries 
  WHERE Row BETWEEN (@PageIndex - 1) * @PageSize + 1 AND @PageIndex * @PageSize

END

Answer 4

You might use something like (pseudo-code):

• counting query: $n = select count(uid) from {users} where ... (your paging condition including userid 123 as the limit)
• $page = floor($n / $pager_size);
• display query: select what,you,want from {users} where (your paging condition without the limit), passed to db_query_range(thequery, $page, $pager_size)

You should really look at pager_query, though, because that's what it's all about, and it basically works like this: a counting query and a display query, except it tries to build the counting query automatically.