4
Inside a table containing the columns data_inicio
, data_fim
, hora_inicio
, hora_fim
need to identify within a set of records the following occurrences: the lowest start date, the highest end date, the lowest start time and the highest end time.
The image below lists a set of records in which I want to check the lowest start_date, highest end_date, lowest start_time, highest end_time:
Based on the records shown in the image above, the lowest start date is simple to achieve, simply use the operator MIN. The same occurs to get the highest end date. I only need to use the operator MAX to get it. To calculate the highest start time and the lowest end time I cannot use the same approach as can be seen in the sql result below.
SQL:
select min(data_inicio), max(data_fim), min(hora_inicio), max(hora_fim)
from minha_tabela
where codigo = 2
Upshot:
To calculate, both the shorter start time and the longer end time need to take into account more than simply the column itself. Simply use the operator MIN and MAX does not solve. I need to take into account also the columns of dates. Looking at the first image we can notice that the lowest start time is '1000', as the lowest date is '04/05'. The biggest final time is '1900', as the biggest final date is '20/05'.
Is there any way to implement this type of query simply through SQL? If so, how?
This example is in SQL Fiddle.
Lucio, I ended up creating my example in SQL Fiddle with the Mysql database which led you to create your answer based on that database. There’s a way you can adapt your answer to Oracle. Use this Sqlfiddle http://sqlfiddle.com/#! 4/d1f13.
– Geison Santos
@Geisonsantos I edited the answer!
– Lucio Rubens
Lucius, congratulations! Very good your solution. Had achieved in another way a little more verbose. Anyway, your solution got better. I will post my reply later to show others another alternative.
– Geison Santos
Thanks man :) @Geisonsantos
– Lucio Rubens