0

I have a table with 2 columns:

  • id INT PRIMARY_KEY
  • y FLOAT

The value in column "y" is guaranteed to be in 2 ranges:

  • small values: [0.18 - 0.20]
  • big values: [2.3 - 2.4]

It is known what "y" column in table has the following pattern: several records with small values then several records with big values then again several small and several big and so on. The number of consecutive records with the same range is not known and it is not fixed (vary). I need to find the id's (PK column) for beginning and ends of all intervals. Is it possible to do it in SQL?

(1; 0.19) (2; 0.18) (3; 0.19) (4; 2.3) (5; 2.4) (6; 2.3) (7;0.19)

Expect output: (1, 'start of small'), (3, 'end of of small'), (4, 'start of big'), (6, 'end of big'), (7, 'start of small')

Matt
  • 13,833
  • 2
  • 16
  • 28
user510040
  • 159
  • 2
  • 10
  • Could you provide a sample output? I'm not sure that your description alone is enough to illustrate your goal. – Nick Aug 17 '16 at 18:51
  • 1
    there is a way to do almost anything in sql :) Please tag your specific rdbms/database sql-server, oracle, mysql, etc.? because it will definitely matter. Also in your example define what is consecutive, every tenth? Including sample data and desired result will get you answers and attention of others much faster. – Matt Aug 17 '16 at 18:55
  • also what if there is a tie for the value should that break the sequence of would it just be ignored? – Matt Aug 17 '16 at 18:56
  • Extra information: this is MySQL. Example of input data: (1; 0.19) (2; 0.18) (3; 0.19) (4; 2.3) (5; 2.4) (6; 2.3) (7;0.19) Expect output: (1, 'start of small'), (3, 'end of of small'), (4, 'start of big'), (6, 'end of big'), (7, 'start of small'). To Matt: the number of consecutive records with same value range is not known and vary; this is the core of this problem. – user510040 Aug 17 '16 at 19:36
  • To Matt: let me re-formulate the question in a way where decimal point precision does not matter: let say "y" column is integer; it has only 0 and 1 values; select y from table return 000011111001100011 (1 digit per line, I concatenated records in one string to save space here). How to find the start and end id's for every interval of 0's or 1's using SQL? – user510040 Aug 17 '16 at 22:12
  • here you go sometimes searches are your best friend. http://stackoverflow.com/questions/23205281/mysql-count-of-records-with-consecutive-months http://stackoverflow.com/questions/11539065/check-for-x-consecutive-days-given-timestamps-in-database – Matt Aug 17 '16 at 22:30
  • Matt - it solves my issue. Thank you! – user510040 Aug 18 '16 at 16:29

0 Answers0