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 |
------------ ----- --------
CodePudding user response：
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.
CodePudding user response：
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)
CodePudding user response：
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;