Is it possible to ORDER BY a SELECT MIN over a join?
Assume the following schema of events and related occurrences:
CREATE TABLE "events" (
"id" integer NOT NULL PRIMARY KEY,
"name" varchar(255) NOT NULL
);
CREATE TABLE "event_occurrences" (
"id" integer NOT NULL PRIMARY KEY,
"event_id" integer NOT NULL REFERENCES "events" ("id"),
"start_date" date NOT NULL,
"end_date" date NOT NULL,
"start_time" time,
"end_time" time
);
Given a date X, events that end on or after that date need to be selected.
That is easy:
SELECT DISTINCT "name"
FROM "events"
INNER JOIN "event_occurrences"
ON ("events"."id" = "event_occurrences"."event_id")
WHERE "event_occurrences"."end_date" >= "2009-10-02";
However, what is not easy, is to select the first event occurrence on or
after date X and order the results by that.
By "first occurrence" the obvious following is meant:
SELECT MIN("start_date")
FROM "event_occurrences"
WHERE "event_occurrences"."start_date" >= "2009-10-02";
However, I fail to grasp how to properly connect the SELECT MIN with ORDER_BY.
Thanks in advance,
HG