SQL to find the maximum of a value between two rows in a table sorted chronologically

I have a table which contains chronological events. They have a开发者_如何转开发 type, time and height.

The data can be summarized like so:

ID, Type, Time, Height  
1, A, time, 0  
2, XX, time, 0   
3, B, time, 3  
4, B, time, 6  
5, C, time, 0  
6, XX, time, 0  
7, A, time, 0  
8, C, time, 0  
9, A, time, 0  
10, B, time, 2  
11, C, time, 0  

etc ( the time column is sorted in ascending order)

i would like to find a SQL statement to list all types of A/B/C where B is the maximum of the height column between types A and C. So the output would look like:

1, A, time, 0   
4, B, time, 6  
5, C, time, 0  
7, A, time, 0  
8, C, time, 0  
9, A, time, 0  
10, B, time, 2  
11, C, time, 0  

The A/B and C will always be in correct order (i.e. B will always be between A and C), But there may not be a B at all, or there may be multiple B's between A and C.

The output may/may not list a B event with NULL data if there is no B between A and C. There is guaranteed to be a C after every A type event.

All XX events shall be ignored in the output. Timestamps over the list will never be duplicated - no two events will contain the same time.

Im guessing to use the MAX function somewhere, and to select all B Rows between A and C depending on the time of A and C.

TIA

---

Not sure if I have this 100% right, but I find that it's always best to break this stuff down into smaller queries into temp tables. Here's a crack at it... (BTW - this is SQL Server T-SQL)

-- get all the type 'a' and type 'c' IDs to represent time spans
if object_id('tempdb..#tab_ac') is not null drop table #tab_ac
select
    a.ID as A_ID,
    (
        select top 1 c.ID
        from tab c
        where c.Time > a.Time
        and c.Type = 'C'
        order by c.Time
    ) as C_ID
into
    #tab_ac
from
    tab a
where
    a.Type = 'A'

create index ix_#tab_ac on #tab_ac (A_ID, C_ID)    

-- get the id with the max height between those spans
if object_id('tempdb..#result1') is not null drop table #result1
select
    ac.*,
    (
        select x.ID
        from tab x
        where x.Time between ta.Time and tc.Time
        order by a.Height desc
    ) as ID_With_Max_Height
into
    #result1
from
    #tab_ac ac join
    tab ta on ac.A_ID = t.ID join
    tab tc on ac.C_ID = t.ID

-- see if that id is type 'B'
select
    *
from
    #result1 r join
    tab t on r.ID_With_Max_Height = t.ID
where
    t.Type = 'B'

Depending on how you want to handle ties for max height, you may want to modify that second query's ORDER BY clause. Good luck.

---

select * from table as t1 where `type`!='XX' and height=(
    select MAX(height) from table as t2 where type=t1.type
) order by id

---

I think this works. What I did was first build time ranges that identify the times between each A and C, then grouped the Bs together into those ranges and extracted the record with the maximum height from each, then unioned that back to the raw entries for A and C. You can carve out cte_source_data and replace references to it with your actual table. I wrote and tested this in PostgreSQL 9.0, but it should work in any mainstream database with slight modification for particular SQL dialects.

with cte_source_data as
(
    select
        id, 
        type, 
        timestamp '2010-12-10 21:' || to_char(id, '99') || ':00' as time,
        height
    from
    (
        select 1 as id, 'A' as type, 0 as height union all
        select 2, 'XX', 0 union all
        select 3, 'B', 3 union all
        select 4, 'B', 6 union all
        select 5, 'C', 0 union all
        select 6, 'XX', 0 union all
        select 7, 'A', 0 union all
        select 8, 'C', 0 union all
        select 9, 'A', 0 union all
        select 10, 'B', 2 union all
        select 11, 'C', 0
    ) as data
),
cte_a_to_c_groups as
(
    select 
        row_number() over(order by time) as group,
        time as start_time,
        next_time as end_time 
    from 
    (
        select
            type,
            time,
            lead(type) over(order by time) as next_type,
            lead(time) over(order by time) as next_time
        from 
            cte_source_data
        where
            type in ('A', 'C')
    ) as cte_a_to_c_groups_a
    where
        type = 'A' and next_type = 'C'

)
select
    id,
    type,
    time,
    height
from
(
    select 
        id,
        type,
        time,
        height
    from
        cte_source_data
    where
        type in ('A', 'C')
    union all
    select
        highest_id as id,
        type,
        highest_time as time,
        highest_height as height
    from
    (
        select
            a.id,
            a.type,
            b.group,
            row_number() over(partition by b.group order by a.height desc nulls last) as rn,
            first_value(id) over(partition by b.group order by a.height desc nulls last) as highest_id,
            first_value(time) over(partition by b.group order by a.height desc nulls last) as highest_time,
            first_value(height) over(partition by b.group order by a.height desc nulls last) as highest_height
        from 
            cte_source_data a
        inner join
            cte_a_to_c_groups b
        on
            a.time between b.start_time and b.end_time
        where
            a.type = 'B'
    ) as highest_values
    where
        rn = 1
) as reunification
order by 
    time

Result:

id  type  time                       height
1   A     2010-12-10 21:00:00  1:00  0
4   B     2010-12-10 21:00:00  4:00  6
5   C     2010-12-10 21:00:00  5:00  0
7   A     2010-12-10 21:00:00  7:00  0
8   C     2010-12-10 21:00:00  8:00  0
9   A     2010-12-10 21:00:00  9:00  0
10  B     2010-12-10 21:00:00 10:00  2
11  C     2010-12-10 21:00:00 11:00  0