Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select * from V$VERSION;


BANNER


Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE	11.2.0.2.0	Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production


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)
);
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'
);
1 rows affected

INSERT INTO my_tab VALUES 
(
  'Title - def', 
  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'
);
1 rows affected

INSERT INTO my_tab VALUES 
(
  'Title - abc', 
  TO_DATE('2020-03-18 11:00:00', 'yyyy-mm-dd hh24:mi:ss'), 
  TO_DATE('2020-03-18 11:30:00', 'yyyy-mm-dd hh24:mi:ss'), 
  'Loc1'
);
1 rows affected

INSERT INTO my_tab VALUES 
(
  'Title - def', 
  TO_DATE('2020-03-18 11:00:00', 'yyyy-mm-dd hh24:mi:ss'), 
  TO_DATE('2020-03-18 11:30:00', 'yyyy-mm-dd hh24:mi:ss'), 
  'Loc1'
);
1 rows affected

INSERT INTO my_tab VALUES 
(
  'Title - ghi', 
  TO_DATE('2020-03-18 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 
  TO_DATE('2020-03-18 12:30:00', 'yyyy-mm-dd hh24:mi:ss'), 
  'Loc1'
);
1 rows affected

INSERT INTO my_tab VALUES 
(
  'Title - jkl', 
  TO_DATE('2020-03-18 13:00:00', 'yyyy-mm-dd hh24:mi:ss'), 
  TO_DATE('2020-03-18 13:30:00', 'yyyy-mm-dd hh24:mi:ss'), 
  'Loc1'
);
1 rows affected

INSERT INTO my_tab VALUES 
(
  'Title - mno', 
  TO_DATE('2020-03-18 14:00:00', 'yyyy-mm-dd hh24:mi:ss'), 
  TO_DATE('2020-03-18 14:30:00', 'yyyy-mm-dd hh24:mi:ss'), 
  'Loc1'
);
1 rows affected

SELECT * FROM my_tab
ORDER BY location, start_time, end_time, title;


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


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;


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


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;


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


SELECT 
  DISTINCT start_time,
  FIRST_VALUE (t.title) OVER (PARTITION BY t.start_time ORDER BY t.start_time ASC) AS title
FROM my_tab t
ORDER BY title, t.start_time;


START_TIME
TITLE


18-MAR-20 10.00.00.000000 AM
Title - abc

18-MAR-20 11.00.00.000000 AM
Title - abc

18-MAR-20 12.00.00.000000 PM
Title - ghi

18-MAR-20 01.00.00.000000 PM
Title - jkl

18-MAR-20 02.00.00.000000 PM
Title - mno


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 readable!
-- Again, the SQL keyword "start" must be quoted!
ORDER BY "start", title; 


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


--
-- NOTE: change from FIRST_VALUE to LAST_VALUE to change the eliminated records
--
SELECT 
  DISTINCT
  LAST_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
ORDER BY "start", title;


TITLE
start
end
LOCATION


Title - def
10:00
10:30
Loc1

Title - def
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


--
-- NOTE: change in the ORDER BY to change the eliminated records
--
SELECT 
  DISTINCT
  FIRST_VALUE (t.title) OVER (PARTITION BY t.start_time ORDER BY t.title DESC) 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 readable!
-- Again, the SQL keyword "start" must be quoted!
ORDER BY "start", title; 


TITLE
start
end
LOCATION


Title - def
10:00
10:30
Loc1

Title - def
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

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

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