SQL - find where in a query a particular row will be

Question

I am working on a forum system. I am trying to allow users to see the messages they made. For this link to work, I need to go to the pageon the topic in which they were posted, in which their message is contained, so bookmarks can work, etc. Since this is a new feature in the old forum, I would like to encode it so that the forum system does not have to keep track of each entry, but can simply populate this list automatically.

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

Given the request, where will the line X appear in the request (guaranteed unique combination of identifiers)? Like in, how many lines will I have to compensate to get to it? This will be in a sorted request.

Ideally, I would like to do this using SQL, not PHP, but if this cannot be done in SQL, I think that is the answer. ^ _ ^

thank

Answer 1

hmm this solution makes a few assumptions, but I think that it should work on what you are trying to do, if I understand correctly:

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

this will give you the number of lines in a specific thread (which I assume you have previously calculated) that are equal to or previously published date (specific user post).

Based on this information (for example, the 15th post in this thread), you can calculate on which page the result will be displayed based on the paging values ​​of the forums. i.e

// dig around forum code for number of items per page
$itemsPerPage = 10; // let 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 2

MSSQL, ROW_NUMBER(), ,

, , . , , - .

ph, , , mySQL.

Answer 3

, "". SCOPE_IDENTITY , - , .

Answer 4

, -, ,

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

:

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

rownum .

Answer 5

Troy, , , , , , MySQL , .

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>

PostgreSQL :

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

, - (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

SQL IDENTITY, . .

CREATE TABLE OF QUERY RESULTS WITH IDENTITY COLUMN

INSERT INTO TABLE  
QUERY  
ORDER BY something

- , , / .

- . , ...