MySQL query logic for fetching posts by dates

Question

Can anyone offer suggestions for the logic in a MySQL query that does the following:

• Selects at least 100 posts
• Selects all of the posts in the last month of loaded thoughts, e.g., if 100 posts loads posts half way through September, then the posts for the entire month of September should be loaded instead of cutting it off half way.

Unfortunately, the second option is not something I know is even possible with MySQL, so I'm reaching out for a little help.

Cheers.

Answer 1

just compute the month of the 100th row in a subquery and use the value in BETWEEN condition

What you basically need:

• find the date of the 100th row (date_a)
• find the first day of the month of the date_a (date_b)
• find all rows that are newer than the date_b

Thus,

SELECT created as date_a
FROM posts
ORDER BY created DESC
LIMIT 99, 1

SELECT
  (LAST_DAY(created) + INTERVAL 1 DAY - INTERVAL 1 MONTH) as date_b
FROM posts
ORDER BY created DESC
LIMIT 99, 1

The last query where the latter is used as subquery is your homework =)

Answer 2

I developed an answer that works within SQL Server before realizing you were looking for a mySQL-specific solution. However, I don't have access to a mySQL instance to verify the syntax - I'm sure it's still broken, but really close to what it should be. I'm pretty sure the searchDate concatenation fails and therefore will return unexpected results assuming the syntax is corrected.

This solution assumes your source table is called thoughts, and the important columns are ThoughtID and ThoughtTime. Obviously, you'll want to customize this as necessary:

SQL Server:

DECLARE @Results TABLE (
   ThoughtID   int,
   ThoughtTime datetime)
DECLARE @date       date
DECLARE @minResults int
DECLARE @totResults int

SET @minResults = 100
SET @totResults = (SELECT COUNT(1) FROM thougts)
SET @date       = CONVERT(CHAR(4), YEAR(GETDATE()), 100) + '-' + CONVERT(CHAR(2), MONTH(GETDATE()),120) + '-01'

IF @totResults <= @minResults
BEGIN
   -- Not enough results, grab everything
   SELECT   ThoughtID, ThoughtTime
   FROM     thougts
   ORDER BY ThoughtTime DESC
END ELSE
BEGIN
   WHILE (SELECT COUNT(1) FROM @Results) < @minResults
   BEGIN
      DELETE FROM @Results -- Can be optimized
      SET @date    = DATEADD(m, -1, @date)
      INSERT INTO @Results (ThoughtID, ThoughtTime) (
         SELECT ThoughtID, ThoughtTime
         FROM   thougts
         WHERE  ThoughtTime >= @date)
   END
   SELECT   *
   FROM     @Results
   ORDER BY ThoughtTime DESC
END

mySQL (Untested, needs refinement):

CREATE PROCEDURE TEST()
BEGIN
CREATE TEMPORARY TABLE Results (
   ThoughtID   int,
   ThoughtTime datetime);
DECLARE searchDate date;
DECLARE minResults int;
DECLARE totResults int;

SET minResults = 100;
SET totResults = (SELECT COUNT(1) FROM thougts);
SET searchDate = CONVERT(YEAR(CURRENT_TIMESTAMP()), CHAR(4)) + '-' + CONVERT(MONTH(CURRENT_TIMESTAMP()), CHAR(2)) + '-01';

IF totResults <= minResults
THEN
   SELECT   ThoughtID, ThoughtTime
   FROM     thougts
   ORDER BY ThoughtTime DESC;
ELSE
   WHILE (SELECT COUNT(1) FROM Results) < minResults DO
      DELETE FROM Results; -- Can probably be optimized
      SET searchDate = DATE_SUB(searchDate, INTERVAL 1 MONTH);
      INSERT INTO Results (ThoughtID, ThoughtTime) (
         SELECT ThoughtID, ThoughtTime
         FROM   thougts
         WHERE  ThoughtTime >= searchDate);
   END WHILE;
   SELECT *
   FROM Results
   ORDER BY ThoughtTime DESC;
END IF;
)
END

If I get access to a mySQL instance within the next few days, I'll try to clean this up more. Otherwise, hopefully another SO user can take a look.