title: SQL Server CE 4.0 select consecutive numeric field
body: <p>I am back trying to get this to work (after a couple years). It's for SQL Server CE 4.0.</p>
  
  <p>Below is some test script that anyone can copy and run to test.</p>
  
  <p>The <code>@Keyword</code> table would be what the user inputs.</p>
  
  <p>These would be the song ID's. Each show has song ID's and a SongOrder column.</p>
  
  <p><code>SongOrder</code> must be consecutive.</p>
  
  <p>To test this, change the order of <code>@Keywords</code> and the <code>ShowId</code> remains and it should not.</p>
  
  <p>Must find a show with that particular song list in that particular order.</p>
  
  <p>So, the user is looking for a show(s) with that particular song order. </p>
  
  <p>Each TABLE param created in the test script mirrors the actual database.</p>
  
  <p>The <code>SELECT</code> should only return 1546 but it's not playing nice.</p>
  
  <pre><code>--Test two shows, years apart
  DECLARE @Shows TABLE (ID INT)
  
  INSERT INTO @Shows (ID) VALUES (1)
  INSERT INTO @Shows (ID) VALUES (1546)
  
  --Add a few songs
  DECLARE @Songs TABLE (ID INT, Name VARCHAR(256))
  
  INSERT INTO @Songs (ID, Name) VALUES (1, 'Song 1')
  INSERT INTO @Songs (ID, Name) VALUES (2, 'Song 2')
  INSERT INTO @Songs (ID, Name) VALUES (3, 'Song 3')
  INSERT INTO @Songs (ID, Name) VALUES (654, 'Song 4')
  INSERT INTO @Songs (ID, Name) VALUES (321, 'Song 5')
  INSERT INTO @Songs (ID, Name) VALUES (322, 'Song 6')
  
  --Where the shows and songs get their reference
  DECLARE @ShowSongRef TABLE (ID INT, ShowID INT, SongID INT, SongOrder INT, PRIMARY KEY (ID))
  
  --Show 1
  INSERT INTO @ShowSongRef (ID, ShowID, SongID, SongOrder) VALUES (1, 1, 1, 1)
  INSERT INTO @ShowSongRef (ID, ShowID, SongID, SongOrder) VALUES (2, 1, 2, 2)
  INSERT INTO @ShowSongRef (ID, ShowID, SongID, SongOrder) VALUES (3, 1, 321, 3)
  INSERT INTO @ShowSongRef (ID, ShowID, SongID, SongOrder) VALUES (4, 1, 3, 4)
  --Show 1546
  INSERT INTO @ShowSongRef (ID, ShowID, SongID, SongOrder) VALUES (5, 1546, 3, 1)
  INSERT INTO @ShowSongRef (ID, ShowID, SongID, SongOrder) VALUES (6, 1546, 1, 2)
  INSERT INTO @ShowSongRef (ID, ShowID, SongID, SongOrder) VALUES (7, 1546, 654, 3)
  INSERT INTO @ShowSongRef (ID, ShowID, SongID, SongOrder) VALUES (8, 1546, 321, 4)
  INSERT INTO @ShowSongRef (ID, ShowID, SongID, SongOrder) VALUES (9, 1546, 322, 5)
  
  --The song order we are looking for
  --Doesn't matter where in the show the order occurs
  DECLARE @Keyword TABLE (ID INT)
  INSERT INTO @Keyword (ID) VALUES ('654')
  INSERT INTO @Keyword (ID) VALUES ('321')
  INSERT INTO @Keyword (ID) VALUES ('322')
  
  --This should only return 1546
  SELECT DISTINCT sh.ID AS ShowID--, s.Name 
  FROM @Songs s
  INNER JOIN @ShowSongRef ref ON ref.SongID = s.ID
  
  --Comment out this join or remove the +1 and it works but in any order
  INNER JOIN @ShowSongRef ref2 ON ref2.SongID = s.ID
          AND ref2.SongOrder = ref.SongOrder + 1 --remove the +1 and it works but are they in order? Not always
          AND ref2.ShowID = ref.ShowID
  
  INNER JOIN @Shows sh ON sh.ID = ref.ShowID
  WHERE ref.SongID IN (SELECT ID FROM @Keyword)
  
  GROUP BY sh.ID
  HAVING COUNT(sh.ID) = (SELECT COUNT(ID) FROM @Keyword)
  --ORDER BY ref2.SongOrder
  
  --Couldn't get this to work either
  --SELECT 
  --  ShowID
  --FROM
  --  @ShowSongRef m
  --CROSS JOIN
  --   (SELECT COUNT(ID) AS consec FROM @Keyword) x
  --WHERE 
  --  EXISTS
  --     (SELECT 1 FROM @ShowSongRef ref
  --          INNER JOIN @Shows sh ON sh.ID = ref.ShowID
  --          WHERE ref.SongID IN (SELECT ID FROM @Keyword)
  --          AND ref.SongOrder = m.SongOrder - x.consec + 1
  --          )
  </code></pre>