XF 1.3 How to find 500,000th post?

[jakew009]

I am trying to find the 500,000th post on our forum to award the member a prize.

We know roughly the day that we passed this milestone (last Saturday, around 7PM)

I have tried the following SQL

SELECT * FROM xf_post WHERE message_state = 'visible'  ORDER BY post_date LIMIT 1 OFFSET 499999;

But it doesn't come up with what I would expect (this post is about 10 days out / early).

What am I missing?
Thanks

 

[Paul B]

http://xenforo.com/community/posts/500000

 

[jakew009]

http://xenforo.com/community/posts/500000

That's not the 500,000th post though, since it includes all the spam and deleted posts!

 

[Paul B]

It's still the 500,000th post.

What happens if you award it to the member who has made the currently visible 500k post and then a post is deleted?
That post is no longer the correct post.

 

[jakew009]

It's still the 500,000th post.

What happens if you award it to the member who has made the currently visible 500k post and then a post is deleted?
That post is no longer the correct post.

Yes of course that could happen, but members only have 24 hours to delete their posts so it's unlikely to happen now

I don't understand why the 500,000th record in the database is not the 500,000th post (if you exclude the 'non visible' posts?

 

[Paul B]

Hard deleted posts, merged posts, etc.