SQL - Find where in a query a certain row will be

I'm working on a forums system. I'm trying to allow users to see the posts they've made. In order for this link to work, I'd need to jump to the page on the particular topic they posted in that contained their post, so the bookmarks could work, etc. Since this is a new feature on an old forum, I'd like to code it so that the forum system doesn't have to keep track of every post, but can simply populate this list automatically.

I know how to populate the list, but I need to do this:

Given a query, where will X row within the query (guaranteed to be unique by some combination of identifiers) appear? As in, how many rows would I have to offset to get to it? This would be in a sorted query.

Ideally, I'd like to do this with SQL and not PHP, but if it can't be done in SQL I guess that's an answer too. ^_^

Thanks

From stackoverflow Nicholas Flynt

• The thing about databases is that there is no real "order" to them. You can use the SCOPE_IDENTITY operator to return the unique ID of the inserted record, then write some sort of function to paginate until that record is found.

  From SauceMaster

• If you're using MSSQL, you could use ROW_NUMBER() function to add an auto-incrementing number to each row in a query.

  I don't know what good that would do you though. But it will do what you asked -- assign a number to the position of a row within the result set of a given query.

  If this is written in ph though, you're probably using mySQL.

  From Troy Howard

• hmm this solution makes a few assumptions, but i think it should work for what you're trying to do if i understand it correctly:

  SELECT count(post_id) FROM posts
    WHERE thread_id = '{$thread_id}' AND date_posted <= '{$date_posted}'
  

  this will get you the number of rows in a particular thread (which i assume you've pre-calculated) which are equal to, or earlier than the date posted (the specific user post in question).

  based on this information (say 15th post in that thread), you can calculate what page the result would be on based on the forums paging values. ie

  // dig around forum code for number of items per page
  $itemsPerPage = 10; // let's say
  $ourCount = getQueryResultFromAbove(); 
  
  // this is the page that post will be on
  $page = ceil($ourCount / $itemsPerPage);
  
  // for example
  $link = '/thread.php?thread_id='.$thread_id.'&page='.$page;
  

  Cervo : I like this because it doesn't get all the rows in one query...

  Nicholas Flynt : Oohhh... clever indeed. Thanks!
  From Owen

• Expanding on Troy's suggestion, you'd need a sub-query, basically,

   select row_number() OVER(ORDER BY MessageDate DESC) 
   AS 'RowNum', * from MESSAGES
  

  then put an outer select to do the real work:

    select RowNum, Title, Body, Author from (
    select row_number() OVER(ORDER BY MessageDate DESC) 
    AS 'RowNum', * from MESSAGES)
    where AuthorID = @User
  

  Use rownum to calculate the page number.

  From James Curran

• I agree with Troy, you probably are going around this wrongly, to fix it we'd have to know more details, but in any case in MySQL you can do that like this

  SET @i=0;
  SELECT number FROM (SELECT *,@i:=@i+1 as number FROM Posts 
  ORDER BY <order_clause>) as a WHERE <unique_condition_over_a>
  

  In PostgreSQL you could use a temporary sequence:

  CREATE TEMPORARY SEQUENCE counter;
  SELECT number FROM (SELECT *,nextval('sequence') as number FROM Posts 
  ORDER BY <order_clause>) as a WHERE <unique_condition_over_a>
  

  From Vinko Vrsalovic

• I think you mean something like this (MySQL)?

  START TRANSACTION;
  
  SET @rows_count = 0;
  SET @user_id = ...;
  SET @page_size = ...;
  
  SELECT 
       @rows_count := @rows_count + 1 AS RowNumber
      ,CEIL( @rows_count / @page_size ) AS PageNumber
  FROM ForumPost P
  WHERE 
      P.PosterId = @user_id;
  
  ROLLBACK;
  

  James Curran : You jump from @row_number to @row_count, but ignoring that, wouldn't it just be easier to initialize @row_count to 0, and skip the IFNULL?

  Kris : Yes, you are right. but in my defense; I was too low on caffeine when i wrote this.
  From Kris

• Most SQL platforms have a proprietary extension of IDENTITY columns or sequences that increment with every item in a table. Most also have temporary tables.

  CREATE TABLE OF QUERY RESULTS WITH IDENTITY COLUMN

  INSERT INTO TABLE
  QUERY
  ORDER BY something

  then the identity column is the number in the query and it tells you how many entries before/after it.

  The important thing is to order by something. Otherwise you may get different orders each query in which case your number means nothing...

  From Cervo