Я хотел бы получить в одном запросе сообщение и первый комментарий, связанный с этим сообщением. Вот как я это делаю в PostgreSQL:
SELECT p.post_id,
(select * from
(select comment_body from comments where post_id = p.post_id
order by created_date asc) where rownum=1
) the_first_comment
FROM posts p
и он отлично работает.
Однако в Oracle я получаю сообщение об ошибке ORA-00904 p.post_id: недопустимый идентификатор.
Кажется, что это нормально для одного подзаголовка, но я не могу получить комментарий только с одним из-за того, что мне нужно использовать rownum (без ограничения/смещения в Oracle).
Что я здесь делаю неправильно?
Нет, Oracle не коррелирует подзапросы, вложенные более чем на один уровень (а также не MySQL).
Это хорошо известная проблема.
Используйте это:
SELECT p.post_id, c.*
FROM posts
JOIN (
SELECT c.*, ROW_NUMBER() OVER (PARTITION BY post_id ORDER BY created_date ASC) AS rn
FROM comments c
) c
ON c.post_id = p.post_id
AND rn = 1
Если вам нужен SQL-модуль, независимый от платформы, это будет работать:
SELECT p.post_id
, c.comment_body
FROM posts p
, comments c
WHERE p.post_id = c.post_id
AND c.created_date IN
( SELECT MIN(c2.created_date)
FROM comments c2
WHERE c2.post_id = p.post_id
);
Но он предполагает, что (post_id, created_date) является основным ключом комментариев. Если это не так, вы получите более одного столбца с комментариями с тем же созданным_дателем.
Кроме того, он, вероятно, будет медленнее, чем решение, которое использует аналитику, заданную Quassnoi.