The following query resturns the result below:
SELECT MIN(Chl.Channel_Id) Channel_Id,
MIN(Chl.Channel_Name) Channel_Name,
MIN(Evt.On_Date) On_Date,
MIN(To_Char(Evt.On_Date, 'DD/MM/YYYY')) Date_String,
Evt.Original_Start_Time Start_Time
FROM Channel Chl,
Event Evt,
Event_Run Evt_Run,
Event_Composition Evt_Comp,
Event_Program Evt_Prog,
Event_Technical_Data Evt_Tech_Data,
Prog_Media Prg_Media
WHERE Evt.On_Date >= '01/mar/08'
AND Evt.On_Date <= '02/mar/08'
AND Evt.Channel_Id = Chl.Channel_Id
AND Evt.Event_Id = Evt_Run.Event_Id
AND Evt_Run.Detail_Id = Evt_Comp.Detail_Id
AND Evt_Run.Event_Composition_Id = Evt_Comp.Event_Composition_Id
AND Evt_Comp.Detail_Id = Evt_Prog.Detail_Id
AND Evt.Event_Technical_Data_Id =
Evt_Tech_Data.Event_Technical_Data_Id(+)
AND Evt_Tech_Data.Content_Id = Prg_Media.Prog_Media_Id(+)
AND Chl.Channel_Id = 5000
AND Prg_Media.Prog_Media_Name = ‘TEST’
GROUP BY evt.original_start_time
ORDER BY Channel_Name,
On_Date
CHANNEL_ID CHANNEL_NAME ON_DATE DATE_STRING START_TIME
5000 Test1 01/03/2008 01/03/2008 1440000
5000 Test1 01/03/2008 01/03/2008 1980000
5000 Test1 02/03/2008 02/03/2008 900000
5000 Test1 02/03/2008 02/03/2008 1350000
When I do a select min for the time as below, I get the result that follow:
SELECT MIN(Chl.Channel_Id) Channel_Id,
MIN(Chl.Channel_Name) Channel_Name,
MIN(Evt.On_Date) On_Date,
MIN(To_Char(Evt.On_Date, 'DD/MM/YYYY')) Date_String,
MIN(Evt.Original_Start_Time) Start_Time
FROM Channel Chl,
Event Evt,
Event_Run Evt_Run,
Event_Composition Evt_Comp,
Event_Program Evt_Prog,
Event_Technical_Data Evt_Tech_Data,
Prog_Media Prg_Media
WHERE Evt.On_Date >= '01/mar/08'
AND Evt.On_Date <= '02/mar/08'
AND Evt.Channel_Id = Chl.Channel_Id
AND Evt.Event_Id = Evt_Run.Event_Id
AND Evt_Run.Detail_Id = Evt_Comp.Detail_Id
AND Evt_Run.Event_Composition_Id = Evt_Comp.Event_Composition_Id
AND Evt_Comp.Detail_Id = Evt_Prog.Detail_Id
AND Evt.Event_Technical_Data_Id =
Evt_Tech_Data.Event_Technical_Data_Id(+)
AND Evt_Tech_Data.Content_Id = Prg_Media.Prog_Media_Id(+)
AND Chl.Channel_Id = 5000
AND Prg_Media.Prog_Media_Name = ‘TEST’
ORDER BY Channel_Name,
On_Date
This returns the absolute min start date and the absolute min start time.
CHANNEL_ID CHANNEL_NAME ON_DATE DATE_STRING START_TIME
5000 Test1 01/03/2008 01/03/2008 900000
What I am trying to get, though is the min start date of the parameters selected and and the min start time within that date, like this:
CHANNEL_ID CHANNEL_NAME ON_DATE DATE_STRING START_TIME
5000 Test1 01/03/2008 01/03/2008 1440000
Someone very kindly suggested to do something like:
select * from table#
where to_date(date# || ' ' || time#, 'yyyy/mm/dd hh24:mi') = (
select min(to_date(date# || ' ' || time#, 'yyyy/mm/dd hh24:mi')) as datetime from table#)
But I have tried in different ways and I cannot understand how to apply it to my query.
Can anyone suggest something, please?
Thanks a lot