0

I don't know if I can do what I expect but hey, at least I can ask you guys...

Here's the thing : I'm building a website with some calendars, and we can add some "dates / rendez vous" on them. For some purpose, I want to get the futur (or actual) closest rendez-vous of each calendars.

I'd like to do it with one query instead of using a loop with one query on each iteration (ie for each calendar), and I want to get only ONE rendez-vous for each calendar and not all the futur ones (which could be... a lot).

So far, I started like that :

SELECT p_id, p_cid, p_start, p_end FROM p WHERE p_end >= :date_of_today

I was thinking about using LIMIT, but I can't see how to put a LIMIT (like 0,1 for each calendar)...

I guess I will have to use a loop but if someone could give me an other solution :)

EDIT

With more informations :

I have a table for calendars, which is like c_id, c_title, c_owner, and a table for the rendez-vous, which is like p_id, p_cid, p_uid, p_start, p_end.

The rendez-vous can last more than one day. To be more specific, a "rendez-vous" is at least a day : so p_start and p_end are a date/timestamp like dd-mm-yyyy 00:00:00. They can be equal as they can be two differents days.

Vae
  • 636
  • 1
  • 8
  • 16
  • Basically you need to rank rows in every calendar according to DESC order and select only top 10 etc. It would be easy with window functions but MySQL does not have them so far. Try to look at this - http://stackoverflow.com/questions/3333665/rank-function-in-mysql - it is about replacement for rank in MySQL – JosMac Jan 10 '17 at 08:39
  • How is the set of calendars defined? Please add more details to your question, especially the table definition and the data logic. – arkascha Jan 10 '17 at 08:40
  • Sorry guys, as you figured out I barrely speak english... JosMac : I have trouble to understand this "rank rows" thing.. Have to check it deeper, thx for the advice. Arkascha : what do you mean by "the set of calendars" ? – Vae Jan 10 '17 at 08:45
  • If I understand you correctly, you are looking for the "groupwise minimum". Have a look at [The Rows Holding the Group-wise Maximum of a Certain Column](http://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html), and add your `where` condition everywhere. There are slightly faster versions of this, but that should be your second concern. – Solarflare Jan 10 '17 at 11:14
  • @Solarflare that sounds very good ! I'll take a look. Thx ! – Vae Jan 10 '17 at 12:15

0 Answers0