Skip to content

Instantly share code, notes, and snippets.

@JunichiIto JunichiIto/gist:8588344
Last active Jan 4, 2016

Embed
What would you like to do?
WITH events_with_schedule AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY e.id ORDER BY s.due_date) AS rowno
,e.id AS event_id
,e.name
,s.id AS schedule_id
,s.due_date
FROM
schedules s
INNER JOIN events e
ON s.event_id = e.id
WHERE
s.due_date >= (SELECT MAX(sysdate) FROM sysdate_dummy)
UNION ALL
SELECT
1 AS rowno
,e.id AS event_id
,e.name
,NULL AS schedule_id
,NULL AS due_date
FROM
events e
WHERE
NOT EXISTS (
SELECT * FROM schedules s WHERE e.id = s.event_id
)
)
SELECT
ews.event_id
,ews.name
,ews.schedule_id
,TO_CHAR(ews.due_date, 'yyyy/mm/dd') AS due_date
FROM
events_with_schedule ews
WHERE
ews.rowno = 1
ORDER BY
ews.due_date
,ews.event_id;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.