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

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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;

Answer 4

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.

Answer 5

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>

Answer 6

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;

Answer 7

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...