Determine Millionth Post

[IPv6Freely]

Hi everyone!

Is there a way for me to determine what was the millionth post on our forum? We just crossed that a month or so ago and would like to send a prize to the person who made that post. Unless it was me, I guess ;) 

Thanks!

[Square Wheels]

Here are some that I partially wrote and others helped me with.

Two options, first shows all posts, last one removes hidden posts.

Set n (999999) to be one less that the record you are looking for

SELECT
    *
FROM
    forums_posts
ORDER BY
    pid
LIMIT 1 OFFSET 999999;

https://stackoverflow.com/questions/16568/how-to-select-the-nth-row-in-a-sql-database-table/16606

---

Does not show hidden posts.  This is what matches the Forum Statistics block.

SELECT (SELECT COUNT(1) FROM `forums_posts` WHERE `queued` = 0) + (SELECT COUNT(1) FROM    `forums_archive_posts` WHERE archive_queued = 0) AS `PostCount`

---

As long as the archive table is empty, this works.

SELECT
    *
FROM
    forums_posts
WHERE `queued` = 0
ORDER BY
    pid
LIMIT 1 OFFSET 999999;

[IPv6Freely]

47 minutes ago, Square Wheels said:

Here are some that I partially wrote and others helped me with.

Two options, first shows all posts, last one removes hidden posts.

Set n (999999) to be one less that the record you are looking for

SELECT
    *
FROM
    forums_posts
ORDER BY
    pid
LIMIT 1 OFFSET 999999;

https://stackoverflow.com/questions/16568/how-to-select-the-nth-row-in-a-sql-database-table/16606

---

Does not show hidden posts.  This is what matches the Forum Statistics block.

SELECT (SELECT COUNT(1) FROM `forums_posts` WHERE `queued` = 0) + (SELECT COUNT(1) FROM    `forums_archive_posts` WHERE archive_queued = 0) AS `PostCount`

---

As long as the archive table is empty, this works.

SELECT
    *
FROM
    forums_posts
WHERE `queued` = 0
ORDER BY
    pid
LIMIT 1 OFFSET 999999;

Thank you!

The Forum Statistics block is giving me the right number:

mysql> SELECT (SELECT COUNT(1) FROM `ibf_forums_posts` WHERE `queued` = 0) + (SELECT COUNT(1) FROM    `ibf_forums_archive_posts` WHERE archive_queued = 0) AS `PostCount`;
+-----------+
| PostCount |
+-----------+
|   1001050 |
+-----------+
1 row in set (0.50 sec)

But the other queries show their latest post on the forums (from this evening) using OFFSET 170823 with a pid of 1126337:

mysql> SELECT pid FROM ibf_forums_posts ORDER BY pid LIMIT 1 OFFSET 170823;
+---------+
| pid     |
+---------+
| 1126337 |
+---------+
1 row in set (0.23 sec)

mysql> SELECT pid FROM ibf_forums_posts ORDER BY pid LIMIT 1 OFFSET 170824;
Empty set (0.05 sec)

It also loks like it jumps from 999998 to 1000022:

mysql> SELECT pid FROM ibf_forums_posts ORDER BY pid LIMIT 1 OFFSET 88016;
+--------+
| pid    |
+--------+
| 999998 |
+--------+
1 row in set (0.03 sec)

mysql> SELECT pid FROM ibf_forums_posts ORDER BY pid LIMIT 1 OFFSET 88017;
+---------+
| pid     |
+---------+
| 1000022 |
+---------+
1 row in set (0.03 sec)

Does that mean the post with pid 1000000 is in a different state (hidden)?

[IPv6Freely]

Hmm that doesn't seem right either. Post with pid of 1000022 is from 2014. So that doesn't seem right either if the current post count is 1001050. I'm sure just a misunderstanding on my part of both how its calculated and how its stored. 

Edited February 14, 2023 by IPv6Freely

[IPv6Freely]

Okay, ChatGPT to the rescue!

Quote

I have two MySQL tables. One of them (table "ibf_forums_posts") has the fields "pid" (which is the primary key), "author_name", and "post_date". The other table ("ibf_forums_archive_posts") has the fields "archive_id" (the primary key), "archive_author_name", and "archive_content_date".

I want to combine the two tables together, sort by the first field (pid and archive_id), and find the 1000000th entry in the list. when selecting from the first table, use "WHERE `queued` = 0" and in the second table use "WHERE `archive_queued` = 0"

 

mysql> SELECT * FROM
    -> (
    ->   SELECT pid AS id, author_name AS author, post_date AS date
    ->   FROM ibf_forums_posts
    ->   WHERE `queued` = 0
    ->   UNION ALL
    ->   SELECT archive_id AS id, archive_author_name AS author, archive_content_date AS date
    ->   FROM ibf_forums_archive_posts
    ->   WHERE `archive_queued` = 0
    -> ) combined
    -> ORDER BY id
    -> LIMIT 999999, 1;
+---------+-------------+------------+
| id      | author      | date       |
+---------+-------------+------------+
| 1125215 | SuperdaveHR | 1670895838 |
+---------+-------------+------------+
1 row in set (14.98 sec)

 

Edited February 14, 2023 by IPv6Freely

[IPv6Freely]

Heh, that user is banned. So I went back one. You have to be kidding me.

mysql> SELECT * FROM (   SELECT pid AS id, author_name AS author, post_date AS date   FROM ibf_forums_posts   WHERE `queued` = 0   UNION ALL   SELECT archive_id AS id, archive_author_name AS author, archive_content_date AS date   FROM ibf_forums_archive_posts   WHERE `archive_queued` = 0 ) combined ORDER BY id LIMIT 999998, 1;
+---------+------------+------------+
| id      | author     | date       |
+---------+------------+------------+
| 1125214 | IPv6Freely | 1670888060 |
+---------+------------+------------+
1 row in set (14.16 sec)

 

[Kirill Gromov]

You can take the author of the previous or next post and call him lucky)

[IPv6Freely]

1 hour ago, Kirill Gromov said:

You can take the author of the previous or next post and call him lucky)

Haha yes we gave it to the next one. It didn't make sense to give it to the 999,999th post anyway, let alone an admin! The first legit post over a million got it :)

I love ChatGPT. And thank you guys for pointing me at the correct tables!

[Gary]