Skip to content

Instantly share code, notes, and snippets.

@JunichiIto
Last active January 4, 2016 07:19
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save JunichiIto/8588344 to your computer and use it in GitHub Desktop.
Save JunichiIto/8588344 to your computer and use it in GitHub Desktop.
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