SQL-запрос — подзапросы при объединении

Аналитические функции, вероятно, лучший ответ на вашу проблему:

SELECT DISTINCT
       cat.*, 
       first_value(lastMale.PLAY_TIME) 
          over (partition by cat.id 
                order by lastMale.PLAY_TIME desc nulls last) maleTime,
       first_value(lastFemale.PLAY_TIME) 
          over (partition by cat.id 
                order by lastFemale.PLAY_TIME desc nulls last) femaleTime,
       first_value(maleName.VALUE)  
          over (partition by cat.id 
                order by lastMale.PLAY_TIME desc nulls last) male,
       first_value(femaleName.VALUE)  
          over (partition by cat.id 
                order by lastFemale.PLAY_TIME desc nulls last) female
...

Просто время последней игры, а не имена котят:

SELECT cat.CAT_ID
     , MAX(play.PLAY_TIME) AS maleTime
     , ( SELECT MAX(playf.PLAY_TIME)
         FROM CAT_KITTEN_PLAY AS playf
         WHERE playf.CAT_ID = cat.CAT_ID
           AND playf.KITTEN_GENDER = 'F'
       ) AS femaleTime
FROM CAT AS cat
  JOIN CAT_KITTEN_PLAY AS play
    ON play.CAT_ID = cat.CAT_ID
WHERE play.KITTEN_GENDER = 'M'
GROUP BY cat.CAT_ID 
HAVING MAX(play.PLAY_TIME) BETWEEN '01-JAN-2011 12:00:00 AM'
                               AND '31-JAN-2011 11:59:59 PM'

Совсем не уверен, что это сработает:

SELECT cat.CAT_ID
     , lastplayM.PLAY_TIME AS maleTime
     , lastplayF.PLAY_TIME AS femaleTime
     , kittenM.VALUE AS male
     , kittenF.VALUE AS female
FROM CAT AS cat
  JOIN 
    ( SELECT CAT_ID
           , KITTEN_GENDER
           , KITTEN_ID
           , MAX(PLAY_TIME) OVER(PARTITION BY CAT_ID, KITTEN_GENDER) AS PLAY_TIME
      FROM CAT_KITTEN_PLAY
      WHERE PLAY_TIME = MAX(PLAY_TIME) OVER(PARTITION BY CAT_ID, KITTEN_GENDER)
    ) AS lastplayM
    ON lastplayM.CAT_ID = cat.CAT_ID
    AND lastplayM.KITTEN_GENDER = 'M'
    AND lastplayM.PLAY_TIME BETWEEN '01-JAN-2011 12:00:00 AM'
                               AND '31-JAN-2011 11:59:59 PM'
  JOIN KITTEN_INFO AS kittenM
    ON kittenM.KITTEN_ID = lastplayM.KITTEN_ID

  LEFT JOIN 
    ( SELECT CAT_ID
           , KITTEN_GENDER
           , KITTEN_ID
           , MAX(PLAY_TIME) OVER(PARTITION BY CAT_ID, KITTEN_GENDER) AS PLAY_TIME
      FROM CAT_KITTEN_PLAY
      WHERE PLAY_TIME = MAX(PLAY_TIME) OVER(PARTITION BY CAT_ID, KITTEN_GENDER)
    ) AS lastplayF
    ON lastplayF.CAT_ID = cat.CAT_ID
    AND lastplayF.KITTEN_GENDER = 'F'
  JOIN KITTEN_INFO AS kittenF
    ON kittenF.KITTEN_ID = lastplayF.KITTEN_ID

Вы можете использовать предложение WITH, чтобы сделать это несколько проще.

WITH MAX_MALE_KITTEN AS
(

     SELECT
        CAT_ID,
        MAX(PLAY_TIME) PLAY_TIME
     FROM
        CAT_KITTEN_PLAY
     WHERE
        KITTEN_GENDER = 'M'
     GROUP BY
        CAT_ID
),
MAX_FEMALE_KITTEN AS
(

     SELECT
        CAT_ID,
        MAX(PLAY_TIME) PLAY_TIME
     FROM
        CAT_KITTEN_PLAY
     WHRE
        KITTEN_GENDER = 'F'
     GROUP BY
        CAT_ID
)

SELECT
    C.CAT_ID,
    F_K_I.NAME LastFemaleName,
    F_PLAY_INFO.PLAY_TIME LastFemalePlayTime,
    M_K_I.NAME LastMaleName,
    M_PLAY_INFO.PLAY_TIME LastMalePlayTime

FROM
    CAT C
    LEFT JOIN CAT_KITTEN_PLAY F_PLAY_INFO
    ON C.CAT_ID = F_PLAY_INFO.CAT_ID
    AND F_PLAY_INFO.KITTEN_GENER= 'F'
    LEFT JOIN MAX_FEMALE_KITTEN M_F_K
    ON F_PLAY_INFO.CAT_ID = M_F_K.CAT_ID
    AND F_PLAY_INFO.PLAY_TIME = M_F_K.PLAY_TIME
    LEFT JOIN KITTEN_INFO F_K_I
    ON F_PLAY_INFO.KITTEN_ID = F_K_I.KITTEN_ID

    INNER JOIN CAT_KITTEN_PLAY M_PLAY_INFO
    ON C.CAT_ID = M_PLAY_INFO.CAT_ID
    AND M_PLAY_INFO.KITTEN_GENER= 'M'
    LEFT JOIN MAX_MALE_KITTEN M_F_K
    ON M_PLAY_INFO.CAT_ID = M_F_K.CAT_ID
    AND M_PLAY_INFO.PLAY_TIME = M_F_K.PLAY_TIME
    LEFT JOIN KITTEN_INFO M_K_I
    ON M_PLAY_INFO.KITTEN_ID = M_K_I.KITTEN_ID
WHERE 
       MAX_MALE_KITTEN.PLAY_TIME    BETWEEN '01-JAN-2011 12:00:00 AM'
                           AND '31-JAN-2011 11:59:59 PM