I'm trying to get the below result from my table.

When the sequence is the same I want to take Min of start_dt and Max of end_dt. But I also have null in the sequence, and I don't want those to be grouped.

I tried with dense rank but it didn't work. Maybe I should be using something like an intermediary table with a new sequence - but I don't know how to achieve that.

How can I achieve this result?

My table:

Expected result:

CodePudding user response：

I see that you grouped by the empty value as well in the expected result image attached. If this is what you need then you can simply use a 'group by' statement.

select Sequence, min(start_dt) as start_dt, max(end_dt) as end_dt from your_table
group by Sequence

However, if you don't want to group by the empty value you can either eliminate it with a 'HAVING' clause with the aforementioned 'group by' statement, or even by using a 'WHERE' clause in an intermediate table then using a 'group by' statement on the resulting table.

select Sequence, min(start_dt) as start_dt, max(end_dt) as end_dt from 
( select * from your_table where Sequence is not null) as s
group by Sequence

CodePudding user response：

You can use union all to combine the grouped results with the individual results that have a null as sequence:

select   id, min(start_dt), max(end_dt), sequence
from     mytable
group by id, sequence
having   sequence is not null
union all
select   id, start_dt, end_dt, sequence
from     mytable
where    sequence is null;

If your database has a way to generate a unique number or has a unique identifier for each row, then you can use that as extra expression in the group_by clause. For instance, in MySQL, you can do:

select   id, min(start_dt), max(end_dt), sequence
from     mytable
group by id, sequence, coalesce(sequence, UUID());