This is an example of where Analytic functions can shine! In this case, you have a choice between the FIRST_VALUE() function (also its "mirror image" - LAST_VALUE()) and the ROW_NUMBER() function! This thread gave me the idea for using FIRST/LAST_VALUE to emulate PostgreSQL's DISTINCT ON. See the fiddle here!
Create and populate the table:
CREATE TABLE my_tab
(
title VARCHAR (25) NOT NULL,
start_time TIMESTAMP NOT NULL,
end_time TIMESTAMP NOT NULL,
location VARCHAR (10) NOT NULL,
CONSTRAINT my_tab_pk PRIMARY KEY (title, start_time, end_time, location),
CONSTRAINT my_tab_st_lt_et_ck CHECK (start_time < end_time)
);
populate (sample):
INSERT INTO my_tab VALUES
(
'Title - abc',
TO_DATE('2020-03-18 10:00:00', 'yyyy-mm-dd hh24:mi:ss'),
TO_DATE('2020-03-18 10:30:00', 'yyyy-mm-dd hh24:mi:ss'),
'Loc1'
);
You have to put in a date as well as a time - Oracle doesn't have a TIME datatype - see here. This thread also proved helpful for final formatting of the answer using the TO_CHAR function.
And then (to check), run:
SELECT * FROM my_tab;
ORDER BY location, start_time, end_time, title;
Result:
TITLE START_TIME END_TIME LOCATION
Title - abc 18-MAR-20 10.00.00.000000 AM 18-MAR-20 10.30.00.000000 AM Loc1
Title - def 18-MAR-20 10.00.00.000000 AM 18-MAR-20 10.30.00.000000 AM Loc1
Title - abc 18-MAR-20 11.00.00.000000 AM 18-MAR-20 11.30.00.000000 AM Loc1
Title - def 18-MAR-20 11.00.00.000000 AM 18-MAR-20 11.30.00.000000 AM Loc1
Title - ghi 18-MAR-20 12.00.00.000000 PM 18-MAR-20 12.30.00.000000 PM Loc1
Title - jkl 18-MAR-20 01.00.00.000000 PM 18-MAR-20 01.30.00.000000 PM Loc1
Title - mno 18-MAR-20 02.00.00.000000 PM 18-MAR-20 02.30.00.000000 PM Loc1
We can see that abc and def overlap at 10:00 and 11:00.
FIRST_VALUE()
In the fiddle there is a first attempt which shows my train of thought - not included here.
The query:
SELECT
DISTINCT
FIRST_VALUE (t.title) OVER (PARTITION BY t.start_time ORDER BY t.start_time ASC) AS title,
TO_CHAR(t.start_time, 'HH24:MI') AS "start", -- be careful, both "start" and "end"
TO_CHAR(t.end_time, 'HH24:MI') AS "end", -- SQL keywords - fails if not quoted!
t.location
FROM my_tab t
-- can have ORDER BY 2, 1, but I think actual names are more legible!
-- Again, the SQL keyword "start" must be quoted!
ORDER BY "start", title;
Result:
TITLE start end LOCATION
Title - abc 10:00 10:30 Loc1
Title - abc 11:00 11:30 Loc1
Title - ghi 12:00 12:30 Loc1
Title - jkl 13:00 13:30 Loc1
Title - mno 14:00 14:30 Loc1
Et voilà - the desired result. If you want to eliminate abc instead of removing abc, you can use the LAST_VALUE() function or change the ORDER BY in the OVER() clause of the query (shown in the fiddle - not included here).
ROW_NUMBER()
I'll run the inner query of my final query first so that my train of thought can be easily followed:
SELECT
m.title,
m.start_time,
m.end_time,
m.location,
ROW_NUMBER() OVER (PARTITION BY m.location, m.start_time ORDER BY m.title, m.start_time, m.location) AS rn
FROM my_tab m;
Result:
TITLE START_TIME END_TIME LOCATION RN
Title - abc 18-MAR-20 10.00.00.000000 AM 18-MAR-20 10.30.00.000000 AM Loc1 1
Title - def 18-MAR-20 10.00.00.000000 AM 18-MAR-20 10.30.00.000000 AM Loc1 2
Title - abc 18-MAR-20 11.00.00.000000 AM 18-MAR-20 11.30.00.000000 AM Loc1 1
Title - def 18-MAR-20 11.00.00.000000 AM 18-MAR-20 11.30.00.000000 AM Loc1 2
Title - ghi 18-MAR-20 12.00.00.000000 PM 18-MAR-20 12.30.00.000000 PM Loc1 1
Title - jkl 18-MAR-20 01.00.00.000000 PM 18-MAR-20 01.30.00.000000 PM Loc1 1
Title - mno 18-MAR-20 02.00.00.000000 PM 18-MAR-20 02.30.00.000000 PM Loc1 1
We can see that we want to hold on to the records where rn = 1, so we wrap this in another query (since we can't put Analytic functions in WHERE clauses).
So, we run the following:
SELECT
t.title,
TO_CHAR(t.start_time, 'HH24:MI') AS "start", -- be careful, both "start" and end are
TO_CHAR(t.end_time, 'HH24:MI') AS "end", -- SQL keywords - fails if not quoted!
t.location
FROM
(
SELECT
m.title,
m.start_time,
m.end_time,
m.location,
ROW_NUMBER() OVER (PARTITION BY m.location, m.start_time ORDER BY m.title, m.start_time, m.location) AS rn
FROM my_tab m
) t
WHERE rn = 1
ORDER BY t.title, t.location, t.start_time;
Result:
TITLE start end LOCATION
Title - abc 10:00 10:30 Loc1
Title - abc 11:00 11:30 Loc1
Title - ghi 12:00 12:30 Loc1
Title - jkl 13:00 13:30 Loc1
Title - mno 14:00 14:30 Loc1
Et voilà encore - the desired result again. If you have a preference for one title over another, you can vary the ORDER BY clause in the OVER part of the ROW_NUMBER() function.
Analytic (aka Window) functions are very powerful and will repay (many, many times over) any effort you put into learning them! As previously mentioned, this thread proved very helpful.
For future reference, henceforth, could you please include your table structure(s) as DDL (CREATE TABLE foo (f1 t1,....);) and your data as DML (INSERT INTO foo VALUES (v1, v2...);)? It's a help to have a single point of truth for the data and saves duplication of effort!
p.s. welcome to the forum!
