הנה חידת SQL קטנה בשביל לפתוח את השבוע שגם עזרה לי לכתוב קצת קוד היום לאתר: נניח שיש לנו טבלא בשם lessons ובה כל השיעורים של קורס מסוים, לטבלא עמודה בשם course_id עם מזהה הקורס ועמודה בשם chapter_id עם מזהה הפרק. לכל שיעור יש גם עמודה בשם duration בה מאוחסן אורך השיעור.

אנחנו רוצים למצוא מהו השיעור הארוך ביותר בכל פרק ובשאילת אחת.

בשביל לקבל תמונה של המידע בואו נתחיל עם שאילתה פשוטה:

select id, chapter_id, course_id, length from lessons where course_id = 2;

 id | chapter_id | course_id | duration
----+-------------------+-----------+--------
 17 |                 3 |         2 |     40
 18 |                 3 |         2 |     35
 20 |                 3 |         2 |     50
 22 |                 4 |         2 |     60
 21 |                 4 |         2 |     65
 19 |                 3 |         2 |     55
 23 |                 3 |         2 |     45
  9 |                 1 |         2 |      1
 24 |                 2 |         2 |     30
 13 |                 2 |         2 |     20
 70 |                 3 |         2 |     43
 71 |                 3 |         2 |     48
 11 |                 1 |         2 |     10
 10 |                 1 |         2 |      5
 12 |                 1 |         2 |     15
 14 |                 2 |         2 |     25
(16 rows)

ואנחנו רוצים לקבל רק את השורות:

 course_id | id | chapter_id | duration
-----------+----+-------------------+--------
         2 | 21 |                 4 |     65
         2 | 19 |                 3 |     55
         2 | 24 |                 2 |     30
         2 | 12 |                 1 |     15
(4 rows)

כלומר לכל פרק אנחנו רוצים רק את השיעור הארוך ביותר באותו פרק.

רעיון ראשון יהיה אולי GROUP BY, אבל מהר מאוד אתם תיזכרו שעם GROUP BY אנחנו מאבדים את כל השדות שלא בקבוצה, ולכן הוא לא יאפשר לנו לשלוף את ה id של השיעורים.

אבל GROUP BY כן יכול לתת לנו את ההתחלה של הפיתרון. קודם כל נשלוף את כל הנתונים בקבוצה לפי הפרק, ואחרי זה נעשה השלמה עם JOIN מהטבלא הראשית.

השליפה הראשונה לפי הפרק נראית כך:

select chapter_id, max(duration) as max_duration from lessons where course_id = 2 group by chapter_id;

 chapter_id | max_duration
------------+--------------
          1 |           15
          2 |           30
          3 |           55
          4 |           65

עכשיו אני יודע מה אתם חושבים: למה שלא תוסיף id לשליפה ונקרא לזה יום? נו, גם אני חשבתי את זה. זה מה שלפוסטגרס היה להגיד בתגובה:

ERROR: column "lessons.id" must appear in the GROUP BY clause or be used in an aggregate function

הוא צודק כמובן. במקום זה אנחנו צריכים להשלים את הנתון בדרך אחרת:

select l.course_id, l.id, f.chapter_id, l.duration
from (
    select chapter_id, max(duration) as max_duration
    from lessons group by chapter_id
) as x
inner join
lessons as l on l.chapter_id = x.chapter_id and l.duration = x.max_duration where l.course_id = 2;

בעזרת JOIN אני משלים את הנתונים החסרים מהטבלא המקורית וכך מקבל את כל המידע.

נ.ב. השליפה הכפולה עם ה JOIN זה מנגנון שחשוב להכיר, אבל סצפיפית אם אתם על פוסטגרס שווה להכיר שיש גם אחלה קיצור דרך שנקרא DISTINCT ON (ובו אני השתמשתי בקוד האמיתי). אותה שליפה תיראה איתו כך:

=> select distinct on(chapter_id) id, chapter_id, duration from lessons where course_id = 2 order by chapter_id, duration desc;

 id | chapter_id | duration
----+-------------------+--------
 12 |                 1 |     15
 24 |                 2 |     30
 19 |                 3 |     55
 21 |                 4 |     65
(4 rows)