1

I have MySql 5.7 and a table "FizzBuzz"

ID Name Sort
1 Foo 1
2 Bar 2
3 Baz 5
4 Quux 6
5 Xyzzy 7
6 Plugh 9

I need to get the records that follow each other in the sort field before the first increment break.

For example,

SELECT Name
FROM FizzBuzz
WHERE sort >= 1 and /* some */

should return only: Foo and Bar

And

SELECT Name
FROM FizzBuzz
WHERE sort > 2 and /* some */

should return only: Baz, Quux and Xyzzy

How can this be done?

lemon
  • 14,875
  • 6
  • 18
  • 38
j. Doe
  • 29
  • 5

3 Answers3

2

In MySQL 5.7, you can use the following approach for this problem:

  • self join your table on t1.ID = t2.ID-1 (you're matching consecutive rows)
  • generate a row number for each non-consecutive row (@num := @num + 1)
  • match your preferred number in a subquery (parts = <your_preferred_number>).
SET @num = 1;

SELECT * 
FROM (SELECT t1.*, 
             CASE WHEN t1.Sort <> t2.Sort+1 THEN @num := @num+1
                  WHEN t2.Sort IS NULL      THEN 1
                  ELSE @num END AS parts
      FROM      tab t1
      LEFT JOIN tab t2 
             ON t1.ID-1 = t2.ID) cte
WHERE parts = <your_preferred_number>

Check the demo here.

lemon
  • 14,875
  • 6
  • 18
  • 38
  • Look like good, but work only for `cte.rn=1`. If set 2/4/8 it's breaks – j. Doe Jul 04 '22 at 16:02
  • No, I meant it doesn't work. See this [demo](https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=c76c2cac5bcbf4601f30bfef65a4177e). The solution does not suit me, but the result is correct – j. Doe Jul 04 '22 at 22:18
  • nope, it's not the same result – j. Doe Jul 04 '22 at 22:51
  • 1
    Ahmed made the right solution, but the request is very complicated, I would like it to be a little simpler – j. Doe Jul 04 '22 at 22:53
  • What about this one? @j.Doe – lemon Jul 04 '22 at 23:09
  • `LEAST(4, @num)` does not work as expected – j. Doe Jul 04 '22 at 23:28
  • Now you can remove it and use just `4`, and in general the value of your choice. – lemon Jul 04 '22 at 23:30
  • `cte.rn = 4` still not working – j. Doe Jul 04 '22 at 23:51
  • There's no `cte.rn = 4` (?). If you intend `parts = 4` then it really depends on what you expect to happen. Given that in your table there's no 4th discontinuation of numbers, the output is empty. – lemon Jul 04 '22 at 23:54
  • yes, ofcourse `parts = 4`. The search should work from any position (any sort number), as in Ahmed's solution – j. Doe Jul 05 '22 at 01:47
  • @j.Doe & lemon Assigning & reading the same user variable in the same select statement is explicitly undefined behaviour, see the manual re those parts of the language. – philipxy Jul 05 '22 at 04:16
1

Try the following:

    SET @rn=0;
    SET @rn2=0;
    
    select T.* from
    (
      select ID, Name, Sort, @rn:=@rn+1 row_number, sort-@rn as cont_num_group
      from FizzBuzz
     ) T
    where T.sort>=1
    and T.cont_num_group =
    (select T2.cont_num_group from
    (
      select sort,@rn2:=@rn2+1 row_number, sort-@rn2 as cont_num_group
      from FizzBuzz 
     ) T2
     where T2.sort>=1 order by T2.sort limit 1
    )
    order by T.sort;

The idea is to find the difference between the Sort field and Row Number, all rows with the same difference will be in an continuous order.

I used select T.* to show all derived fields so you can understand how the query works, you may replace it with select T.Id, T.name.

See a demo from here.

ahmed
  • 9,071
  • 3
  • 9
  • 22
  • @j.Doe, You are welcome. Actually, the complexity of this query raised from the nature of the problem, we are trying to select rows according to a derived field values which we can not predict. So we need to call the query twice, one to select rows and one to set the value of our condition. I am not saying that this is the only way to solve the problem, maybe someone else have a simple one. At the end, we are trying to help each other and it's up to you to accept the answer or wait for another one. Good luck. – ahmed Jul 05 '22 at 14:10
0

Don't know why you want check the order sequence in MySql? You can achieve it like following using Lag() function:

MySQL Window Function Descriptions

Select * from (SELECT ID, LAG([id],1) OVER (ORDER BY [ID]  ) as PreviousRow
From FizzBuzz)tb1 where  PreviousRow - ID < 1

I will suggest use programming way if you need more complicate logical checking instead of using SQL

Aiden Or
  • 70
  • 6
  • The OP appears to be bound to using MySQL 5.7, in which case your query won't work. – Tim Biegeleisen Jul 04 '22 at 01:59
  • It's work only in Mysql 8+. I want check this in Mysql because there can be a lot of records – j. Doe Jul 04 '22 at 02:02
  • IF your MySQL <8.0 ,you need set variable to hold the value for comparison, Pls refer this post: https://stackoverflow.com/questions/11303532/simulate-lag-function-in-mysql – Aiden Or Jul 04 '22 at 02:09