title: Fetch all records before a particular event in SQL
body: <p>I have a table like this <code>DATA</code></p>
  
  <pre><code>+--------+--------+----------+------+
  | TranID | CustID | TransSeq | Type |
  +--------+--------+----------+------+
  |    1   |  100   |      1   | A    |
  |    2   |  100   |      2   | A    |
  |    3   |  100   |      3   | B    |
  |    4   |  200   |      1   | A    |
  |    5   |  200   |      2   | B    |
  |    6   |  200   |      3   | A    |
  |    7   |  200   |      4   | A    |
  |    8   |  200   |      5   | A    |
  +--------+--------+----------+------+
  </code></pre>
  
  <p>I want to fetch all records before <code>Type B</code>. Hence my output will be like this</p>
  
  <pre><code>+--------+--------+----------+------+
  | TranID | CustID | TransSeq | Type |
  +--------+--------+----------+------+
  |    1   |  100   |      1   | A    |
  |    2   |  100   |      2   | A    |
  |    4   |  200   |      1   | A    |
  +--------+--------+----------+------+
  </code></pre>
  
  <p>One way that I can think of is the following</p>
  
  <p><strong>Step 1 - Create temp table storing CustID and TransSeq where type == B</strong></p>
  
  <pre><code>CREATE TABLE TEMP AS
  select CustID, TransSeq as TransSeq_B from DATA 
  where Type = "B"
  </code></pre>
  
  <p>The output of step 1 looks like this</p>
  
  <pre><code>+--------+------------+------+
  | CustID | TransSeq_B | Type |
  +--------+------------+------+
  |  100   |      3     | B    |
  |  200   |      2     | B    |
  +--------+------------+------+
  </code></pre>
  
  <p><strong>Step 2 - Merge TEMP with DATA using CustID</strong></p>
  
  <pre><code>CREATE TABLE DATA_NEW AS
  select D.TranID, D.CustID, D.TransSeq, D.Type, T.TransSeq_B
  from DATA inner join TEMP on D.CustID = T.CustID
  </code></pre>
  
  <p>The output of step 2 looks like this</p>
  
  <pre><code>+--------+--------+----------+------+------------+
  | TranID | CustID | TransSeq | Type | TransSeq_B |
  +--------+--------+----------+------+------------+
  |    1   |  100   |      1   | A    |       3    |
  |    2   |  100   |      2   | A    |       3    |
  |    3   |  100   |      3   | B    |       3    |
  |    4   |  200   |      1   | A    |       2    |
  |    5   |  200   |      2   | B    |       2    |
  |    6   |  200   |      3   | A    |       2    |
  |    7   |  200   |      4   | A    |       2    |
  |    8   |  200   |      5   | A    |       2    |
  +--------+--------+----------+------+------------+
  </code></pre>
  
  <p><strong>Step 3 - Query this new table from step 2 and keep records where all TransSeq is less than TransSeq_B</strong></p>
  
  <pre><code>select * from DATA_NEW
  where TransSeq &lt; TransSeq_B
  </code></pre>
  
  <p>Is there any efficient way of doing this, as I have many records (>20M)</p>