0

Data is in a table in this format: 

id  value   timestamp
1     112     1234561
1     111     1234562
2     111     1234563
2     111     1234564
1     111     1234565
1     111     1234566
1     112     1234567
2     111     1234568

How to write SQL (Ansi-99) query that would do following:

Give me ID with Starting and Ending timestamp for all unique IDs that have 3 consecutive values=111?

In this case - output of query should be:

1, 1234562  
1, 1234566 
2, 1234563  
2, 1234568  

(or: 
1, 1234562, 1234566
2, 1234563, 1234568
)

Timestamp is always ordered (from lower to higher).
Not looking to work for specific database - just a SQL logic in general (but if it will help - let's say it is MySQL).
Thanks!

Joe
  • 11,983
  • 31
  • 109
  • 183
  • A sql logic would use window functions, similar to [this](https://stackoverflow.com/q/18436749/6248528), but that's neither supported by (core) sql:1999 nor (current) MySQL (nor is the MySQL workaround for window functions, using variables, sql:1999-compliant). You can rewrite window functions with subqueries/joins, see e.g. [here](https://stackoverflow.com/q/35382897/6248528), although you should specify a primary key for that. It's neither fun nor fast nor very useful (so noone will do it for you with these restrictions), but you can try it. If you get stuck, post your code and ask for help. – Solarflare Oct 09 '17 at 23:06
  • @solarflare WITH was part of the standard. Just sayin' – Strawberry Oct 10 '17 at 07:17
  • In the example above, there are no unique ids with 3 consecutive values – Strawberry Oct 10 '17 at 07:20
  • Not sure if I explained good but ID=1 is repeating 3 times in a row with value=111 (for timestamps: 1234562, 1234565, 1234566) – Joe Oct 10 '17 at 20:32

0 Answers0