Mysql get next two dates grouped by ID

Question

I have a table that has all eventIDs and event dates. I need to get the next two dates, beyond the current date, for a specific subset of eventIDs. I have tried many iterations and looked at many posts that return the top N results by GROUP - but I can't get any of those examples to work as those examples aren't using dates like I need to. (ex: Using LIMIT within GROUP BY to get N results per group?)

+------------+-----+
| eDate      | ID  |
+------------+-----+
| 2021-10-27 | 1   |
+------------+-----+
| 2021-11-03 | 1   |
+------------+-----+
| 2021-11-10 | 1   |
+------------+-----+
| 2021-11-17 | 1   |
+------------+-----+
| 2021-11-24 | 1   |
+------------+-----+
| 2021-12-01 | 1   |
+------------+-----+
| 2021-12-08 | 1   |
+------------+-----+
| 2021-10-27 | 2   |
+------------+-----+
| 2021-11-03 | 2   |
+------------+-----+
| 2021-11-10 | 2   |
+------------+-----+
| 2021-11-17 | 2   |
+------------+-----+
| 2021-11-24 | 2   |
+------------+-----+
| 2021-12-01 | 2   |
+------------+-----+
| 2021-12-08 | 2   |
+------------+-----+
| 2021-10-27 | 3   |
+------------+-----+
| 2021-11-03 | 3   |
+------------+-----+
| 2021-11-10 | 3   |
+------------+-----+
| 2021-11-17 | 3   |
+------------+-----+
| 2021-11-24 | 3   |
+------------+-----+
| 2021-12-01 | 3   |
+------------+-----+
| 2021-12-08 | 3   |
+------------+-----+

SELECT eDate,ID,COUNT(*) as eCount FROM myTable WHERE ID in (1,3) AND eDate >=CURDATE() GROUP BY ID,eDate HAVING eCount < 3 ;

This query doesn't work because the GROUP BY ID,eDate creates a unique pair, so its returning ALL entries for IDs 1 and 3 - all with a eCount = 1

Technically I don't actually need the eCount but it illustrates one of my many attempts to get the correct results. If today was 2020-10-28, my desired results would be following.

+------------+-----+--------+
| eDate      | ID  | eCount |
+------------+-----+--------+
| 2021-11-03 | 1   | 1      |
+------------+-----+--------+
| 2021-11-10 | 1   | 2      |
+------------+-----+--------+
| 2021-11-03 | 3   | 1      | 
+------------+-----+--------+
| 2021-11-10 | 3   | 2      |
+------------+-----+--------+

@Akina - I am using mysql 5.7. – rolinger Oct 29 '21 at 13:05 — rolinger, Oct 29 '21 at 13:05

score 0 · Answer 1 · answered Oct 29 '21 at 04:25

I think I figured out a solution...I don't know how well it scales or if there are other caveats I need to think about, but it occurred to me I don't need to use GROUP BY because I am already looking for ID in (1,3). All I need to do count the number of IDs I am looking for and multiple it by 2 to set as the LIMIT

IE: 1,3 = LIMIT 4
SELECT * FROM myTable WHERE ID IN (1,3) AND eDate>=CURDATE() ORDER BY eDate LIMIT 4 ;

IE: 1,3,11, 15 = LIMIT 8
SELECT * FROM myTable WHERE ID IN (1,3,11,15) AND eDate>=CURDATE() ORDER BY eDate LIMIT 4 ;

This will grab ALL dates for those IDs, order them by the date...so the top 4 or top 8 will be all the records I need.

score 0 · Answer 2 · answered Oct 29 '21 at 05:43

Your solution works only if you have 2 or more dates after current date. Observe this example, if one of you id only have 1 date appear after current date, the LIMIT 4 in this query:

SELECT * FROM myTable WHERE ID IN (1,3) AND eDate>=CURDATE() ORDER BY eDate LIMIT 4 ;

.. will populate another row with ID=1. The result might be this instead:

+------------+----+
|   eDate    | ID |
+------------+----+
| 2021-11-03 | 3  |
| 2021-11-03 | 1  |
| 2021-11-10 | 1  |
| 2021-11-17 | 1  |
+------------+----+

I'm guessing that's not what you want.

Following Akina's comment of "Use ROW_NUMBER() in CTE", if you're on MySQL version that supports window function, this could be the query you need:

WITH cte AS (
SELECT eDate, ID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY eDate) eCount
  FROM myTable WHERE eDate>=CURDATE()
  ORDER BY ID, eDate) 
SELECT * FROM cte 
WHERE eCount <=2;

You'll get all ID's date records that appear after current date with the flexibility to define specific ID or not and without the need to use LIMIT. For older MySQL versions that doesn't support window function, you probably can emulate the same idea of using row number like this:

SELECT eDate, ID, rownum
FROM
   ( SELECT *,
           @rn:=CASE WHEN @i = ID AND eDate >= CURDATE()
                   THEN @rn + 1
                   ELSE 0 END AS rownum,
            @i := ID
        FROM myTable m  
         CROSS JOIN (SELECT @i:=0, @rn:=0)  r
       ORDER BY ID, eDate) v
 WHERE rownum > 0 AND rownum <= 2 
ORDER BY ID, eDate;

Demo fiddle

I am on mysql 5.7, so I can't take advantage of new `row_number()` - but I see your point about getting extra rows for a particular ID if another ID only lists one date. I will try out your second solution. and report back. — rolinger, Oct 29 '21 at 10:08

score -1 · Answer 3 · answered Oct 29 '21 at 04:39

You didn't specify the type of eDate, so if the eDate type is DATE (only date without time) I wish this can help you.

SELECT eDate, ID, COUNT(ID) AS eCount
FROM myTable
WHERE ID IN (1,3) -- This condition return just two ids (1 and 3)
        AND eDate BETWEEN CONVERT(DATE, GETDATE()) AND CONVERT(DATE, DATEADD(DAY, 2, GETDATE())) -- This condition return the next two dates, beyond the current date ( current date = GETDATE() )
GROUP BY ID, eDate 
HAVING eCount < 3 -- This condition ensure select rows just have count loser than 3;

But if the eDate type is DATETIME, you must convert it first. CONVERT(DATE, eDate)

this appears to be a SQL query, not mysql. I converted it to mysql but it won't work anyway because your DATEADD is adding 2 more days to the current day....which is not what I am looking for. I am looking for the next two schedule dates beyond the current day. If today is 2020-10-28, your query would return 2021-10-29 & 2021-10-30, when (based on the data set I provided) it would need to return `2021-11-03 & 2021-11-10` — rolinger, Oct 29 '21 at 10:27

Mysql get next two dates grouped by ID

3 Answers3