CODEWITHSUNDEEP
mysqlranking
Bill
Bill

Reputation: 536

Select row based on a rank and previously selected row

I am attempting to do a forum rank system, which can get one record at a time based on the previous record.

The example table is:

| POST_ID | POST_REPLIES |
--------------------------
|   1     |        5     | 
|   2     |        2     | 
|   3     |        8     |
|   4     |        8     |
|   5     |        12    |
--------------------------

If I do a simple query ORDER BY POST_REPLIES DESC, I get POST_ID 5, 4, 3, 1, 2.

But what I want to do is get onlythe next row (so a single row at a time), and based on the post it is currently at.

For example: if I am currently viewing post #3, there would be a button labeled 'next post with most replies' which would point to post # 4.

I am currently having trouble dealing with duplicates, as I run into a loop between 3 and 4 (3 points to 4, and 4 points to 3 rather than 5)

I had played around with joining the table onto itself and comparing the rows to see which one was greater or less, but since I am using a limit of 1, the row is always 1 and thus useless. So the basic query I had was:

SELECT * FROM posts
   WHERE post_id != '$currentPost' 
   ORDER BY POST_REPLIES DESC, POST_ID DESC LIMIT 1

How can I do this?

Upvotes: 3

Views: 433

Answers (3)

newtover
newtover

Reputation: 32094

SELECT p.*
FROM (
  SELECT POST_ID, POST_REPLIES
  FROM posts
  WHERE POST_ID = @currentId) as cur
JOIN posts p 
  ON p.POST_REPLIES >= cur.POST_REPLIES
  AND p.POST_ID > cur.POST_ID
ORDER BY p.POST_REPLIES DESC, p.POST_ID
LIMIT 1 ;

Upvotes: 1

PFY
PFY

Reputation: 336

Limit using a range, for example 

 limit 0,1

where zero is the starting record and one is the number of records to fetch. 

 limit 5,1

would get you the sixth record and so on. You can track the page number via post, get, or session and use it to manipulate the query in this way.

It is also common to fetch and store all the records, then present them per page, however this can be problematic if you expect to generate a large number of records.

Upvotes: 0

Matt Dodge
Matt Dodge

Reputation: 11142

The first step you would need would be to "rank" the results. The best way I have found to do this in MySQL is with a variable like so:

SELECT posts.post_id, posts.post_replies, @rank := @rank + 1 AS rank
FROM posts, (SELECT @rank := 0) r

Then you would probably have to nest that query in another one to accomplish what you need. Let me know if that points you in the right direction

Upvotes: 1

Related Questions