Mysql select rows when selected value is found-CodePudding

This is the Create table statement

CREATE TABLE `numbers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `time` time DEFAULT NULL,
  `date` date DEFAULT NULL,
  `number` float DEFAULT NULL,
  PRIMARY KEY (`id`)
)

I have this table and trying to get some result but without success. In the example above I have:

SELECT * FROM numbers where number <= 15

The ids = 10870 and 10885 indicates are higher than 15.

So I need to count how many rows are between the row 10870 (including this row) and 10885 (excluding this row).

In this case the result was 15, but this can occur many times in the whole rows, so I just need 7 times before now, the interval where number was higher than 15.

I'm trying like this but is not correct, maybe to use substring?

from (select number, count(id) as cnt
      from numbers where number > 15
      group by number
     ) t;

Also tried like this:

WITH n AS (
 SELECT id, number
 FROM (
  SELECT b.id, b.number, ROW_NUMBER() OVER(PARTITION BY a.id ORDER BY b.id ASC) r
  FROM numbers a
  JOIN numbers b ON a.id < b.id
  WHERE a.number > 15
 ) t
)
SELECT number, COUNT(id) counts
FROM n
GROUP BY number

PLEASE don't be rude and close my question if is not clear, just tell me and give me some time to change.

Screenshot

CodePudding user response：

It seems like this is what you're after:

SELECT n.* 
FROM numbers n 
JOIN
   ( SELECT 
           MIN(id) minid,
           MAX(id) maxid
       FROM numbers
      WHERE number > 15) v
 ON n.id >= v.minid
 AND n.id <= v.maxid
 WHERE number > 0
ORDER BY date DESC, time DESC
LIMIT 7;

Or perhaps if you just want what's in between and omit the last value where it's >15 then you can just change

ON n.id >= v.minid
 AND n.id <= v.maxid

ON n.id > v.minid
 AND n.id < v.maxid

Or maybe with another column of COUNT(*) OVER ():

SELECT n.*,
       COUNT(*) OVER ()
FROM numbers n JOIN
(SELECT 
      MIN(id) minid,
      MAX(id) maxid
FROM numbers
WHERE number > 15) v
ON n.id >= v.minid
AND n.id <= v.maxid
WHERE number > 0
ORDER BY date DESC, time DESC
LIMIT 7

Demo fiddle