MySQL Forums
Forum List  »  General

Is it possible to ORDER BY a SELECT MIN over a join?
Posted by: Herbert Grunwald
Date: October 02, 2009 04:16AM

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

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.