Is there a more efficient query for this task?

Question

A message is defined as all the messages between 2 messages with type 0.

I need to find the first block in the table Make sure all the rows exist in the block

My solution :

SELECT MSG FROM (   
    SELECT 
    CASE  
        WHEN type = 83 AND next_type = 84 THEN REPLACE(CONCAT(MSG,next_msg),' ', '')
        WHEN type = 83 AND next_type != 84 THEN MSG
        WHEN type = 83 AND next_type IS NULL THEN MSG       
, ROW_NUMBER() OVER(order by id) AS row_num 

                ) AS tmp5
WHERE MSG IS NOT NULL

Answer 1

Your approach seems very good. You select the first and second zero-type IDs first and only work on the IDs between them. I don't understand why you compare IDs with their relative position (row_num = id), though. This forces you to make a cross join with all messages in the table instead of using a mere inner join, an exists clause or a between clause.

This is a shortened version of your query to look up the messages in the bulk:

with limits as
(
  select min(id) as id01, max(id) as id02
  from (select top 2 id, type from messages where type = 0 order by id) first2
)
select case when next_type = 84 then msg + next_msg else msg end
from
(
  select
    type, lead(type) over(order by id) as next_type,
    msg, lead(msg) over(order by id) as next_msg  
  from messages m
  where id > (select id01 from limits)
    and id < (select id02 from limits)
) firstbulk
where type = 83;

You'd want an index on messages(type, id) to quickly get the zero-type IDs, and an index on messages(id) of course to quickly select the rows in the bulk.

Demo: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=eecb6383b9daa6963e08dde6a0dd30a1

EDIT: You want gap detection built in. Use `COUNT(*) OVER () and the zero-type IDs to see whether there are as many rows between the IDs as expected.

with limits as
(
  select min(id) as id01, max(id) as id02
  from (select top 2 id, type from messages where type = 0 order by id) first2
)
select case when gaps <> 0 then 'Gap detected'
            when next_type = 84 then msg + next_msg 
            else msg end
from
(
  select
    m.type, lead(m.type) over(order by m.id) as next_type,
    m.msg, lead(m.msg) over(order by m.id) as next_msg,
    l.id02 - l.id01 - count(*) over () - 1 as gaps
  from messages m
  join limits l on l.id01 < m.id and l.id02 > m.id
) firstbulk
where type = 83;

Demo: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=909228fd2696b419d14cd4a1c2c220a3

Answer 2

If I follow the logic, then:

select concat(msg, (case when next_type = 84 then next_msg else '' end))
from (select m.*,
             lead(type) over (order by id) as next_id,
             lead(msg) over (order by id) as next_msg,
             sum(case when type = 0 then 1 else 0 end) over (order by id) as num_0s
      from Messages m
     ) m
where num0s % 2 = 1 and   -- in between two 0s
      type = 83;

The counting of 0s provides a way to find the messages between two 0s. The rest is just filtering on type = 83 and doing the concatenation.