title: Selecting Every Other Row & Transposing: Dense Rank, Efficiency?
body: <p><strong>WHAT I HAVE (data generated from a query below):</strong></p>
  <pre><code>| ID     | IND | SIG | START               |
  |--------|-----|----:|---------------------|
  | 1      | atr |   1 | 2018-09-04 11:05:00 |
  | 2      | atr |   3 | 2018-09-04 11:16:00 |
  | 3      | atr |   1 | 2018-09-04 11:17:00 |
  | 4      | atr |   3 | 2018-09-04 11:26:00 |
  | 5      | ema |   3 | 2018-09-04 11:07:00 |
  | 6      | ema |   1 | 2018-09-04 11:09:00 |
  | 7      | ema |   3 | 2018-09-04 11:10:00 |
  | 8      | ema |   1 | 2018-09-04 11:18:00 |
  | 9      | ema |   3 | 2018-09-04 11:20:00 |
  | 10     | ema |   1 | 2018-09-04 11:27:00 |
  | 11     | ema |   3 | 2018-09-04 11:29:00 |
  | 12     | tig |   3 | 2018-09-04 11:12:00 |
  | 13     | tig |   1 | 2018-09-04 11:13:00 |
  | 14     | tig |   3 | 2018-09-04 11:14:00 |
  | 15     | tig |   1 | 2018-09-04 11:15:00 |
  | 16     | tig |   3 | 2018-09-04 11:23:00 |
  | 17     | tig |   1 | 2018-09-04 11:28:00 |
  </code></pre>
  <p><strong>WHAT I NEED:</strong></p>
  <pre><code>| IND | START ID | START SIG | START DT            | END ID | END SIG | END DT              |
  |-----|----------|----------:|---------------------|--------|---------|---------------------|
  | atr | 1        |         1 | 2018-09-04 11:05:00 | 2      | 3       | 2018-09-04 11:16:00 |
  | atr | 3        |         1 | 2018-09-04 11:17:00 | 4      | 3       | 2018-09-04 11:26:00 |
  | ema | 6        |         1 | 2018-09-04 11:09:00 | 7      | 3       | 2018-09-04 11:10:00 |
  | ema | 8        |         1 | 2018-09-04 11:18:00 | 9      | 3       | 2018-09-04 11:20:00 |
  | ema | 10       |         1 | 2018-09-04 11:27:00 | 11     | 3       | 2018-09-04 11:29:00 |
  | tig | 13       |         1 | 2018-09-04 11:13:00 | 14     | 3       | 2018-09-04 11:14:00 |
  | tig | 15       |         1 | 2018-09-04 11:15:00 | 16     | 3       | 2018-09-04 11:23:00 |
  </code></pre>
  <p><strong>DETAILS:</strong></p>
  <p>I need help (transposing?) every other row of generated data from a     query (thanks @Strawberry!) Basically for every id where SIG = 1 with    the same IND, I need the earliest ID/DATE where SIG = 3.</p>
  <p>Each IND grouping and the start/finish combo must start with SIG = 1,    and FINISH w/ SIG = 3 within the same row.</p>
  <p>Missing ID's #5, 12, 17 are due to not following the above point (ie- the grouping might have started with 3 as the earliest date, or     ended on a 1 so those rows are eliminated from the results).</p>
  <p>Data is over 800+mil rows, so the query needs to be efficient. I'm not sure if the original query can be re-written to reflect what's needed, or, if I keep the original query and add to it to manipulate the output into the new format?</p>
  <p><strong>CURRENT QUERY &amp; DATA:</strong></p>
  <p><a href="https://www.db-fiddle.com/f/f31HrAKzpuiF77Ed8R1iSH/0" rel="nofollow noreferrer">FIDDLE LINK</a></p>
  <pre><code>DROP TABLE IF EXISTS my_table;
  
  CREATE TABLE my_table
  (ind CHAR(3) NOT NULL
  ,SIG TINYINT(1) NOT NULL
  ,dt DATETIME NOT NULL
  ,price DECIMAL(6,2) NOT NULL
  ,PRIMARY KEY(ind,dt)
  );
  
  INSERT INTO my_table VALUES
  ('atr', 1, '2018/09/04 11:05:00', 10),
  ('atr', 1, '2018/09/04 11:06:00', 15),
  ('ema', 3, '2018/09/04 11:07:00', 12),
  ('atr', 1, '2018/09/04 11:08:00', 98),
  ('ema', 1, '2018/09/04 11:09:00', 14),
  ('ema', 3, '2018/09/04 11:10:00', 17),
  ('ema', 3, '2018/09/04 11:11:00', 72),
  ('tig', 3, '2018/09/04 11:12:00', 63),
  ('tig', 1, '2018/09/04 11:13:00', 33),
  ('tig', 3, '2018/09/04 11:14:00', 53),
  ('tig', 1, '2018/09/04 11:15:00', 58),
  ('atr', 3, '2018/09/04 11:16:00', 954),
  ('atr', 1, '2018/09/04 11:17:00', 2),
  ('ema', 1, '2018/09/04 11:18:00', 8),
  ('ema', 1, '2018/09/04 11:19:00', 9),
  ('ema', 3, '2018/09/04 11:20:00', 95),
  ('ema', 3, '2018/09/04 11:21:00', 75),
  ('tig', 1, '2018/09/04 11:22:00', 25),
  ('tig', 3, '2018/09/04 11:23:00', 60),
  ('atr', 1, '2018/09/04 11:24:00', 3),
  ('tig', 3, '2018/09/04 11:25:00', 18),
  ('atr', 3, '2018/09/04 11:26:00', 94),
  ('ema', 1, '2018/09/04 11:27:00', 42),
  ('tig', 1, '2018/09/04 11:28:00', 35),
  ('ema', 3, '2018/09/04 11:29:00', 7);
  
  WITH CTE AS
      (
          SELECT
            IND,
            SIG,
            DT,
            (DENSE_RANK() OVER (PARTITION BY IND ORDER BY DT) 
                - DENSE_RANK() OVER (PARTITION BY IND, SIG ORDER BY DT)) GRP
          FROM
              my_table
          ORDER BY
              IND,
              DT,
              SIG
      )
  SELECT
      IND,
      SIG,
      MIN(DT) START
  FROM
      CTE
  GROUP BY
      IND,
      SIG,
      GRP
  ORDER BY
      IND,
      START
  ;
  </code></pre>