CODEWITHSUNDEEP
mysql
MrEyes
MrEyes

Reputation: 13740

Get a row at a specific position in the result set

The following MySQL query returns a result set with just over a million rows:

SELECT postid
FROM post p
JOIN thread t ON p.threadid = t.threadid
WHERE t.forumid = 10
AND t.visible = 1
AND p.visible = 1
ORDER BY p.dateline desc

Which is nice, however I want to get the row at the millionth position.

So the question is, if you haven't guessed already, how do I go about doing this

NOTE : This table is actively being used, so new rows are added all the time.

EDIT : That question isn't entirely clear, the query returns a list of all posts in a specific forum order by post date desc, so it isn't so much the millionth post in the result set rather the millionth post on the forum.

Upvotes: 1

Views: 480

Answers (2)

Gangnus
Gangnus

Reputation: 24474

SELECT 
    postid
    @curRow := @curRow + 1 AS row_number
FROM post p
JOIN (SELECT @curRow := 0) thread t ON p.threadid = t.threadid
WHERE t.forumid = 10
      AND t.visible = 1
      AND p.visible = 1
      AND row_number=1000000
ORDER BY p.dateline desc

So, you'll have the millionth row of the resulting join.

Upvotes: 0

Ian L
Ian L

Reputation: 5601

You can use the LIMIT clause which accepts a zero-based record index followed by a record count.

Therefore for a single record at the millionth row, you could use:

SELECT postid
FROM post p
JOIN thread t ON p.threadid = t.threadid
WHERE t.forumid = 10
AND t.visible = 1
AND p.visible = 1
ORDER BY p.dateline desc
LIMIT 999999,1

Upvotes: 4

Related Questions