How to select only the values that are arranged in order-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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.

CodePudding user response：

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)
filter out rows that have consecutive "sort" values (t1.Sort <> t2.Sort-1)
generate a row number for each non-consecutive row (@num := @num 1), to be used to select how many non-consecutive "sort" values you need (cte.rn = <your preferred number>)
join this result set back with your original table with an inequality sign (tab.Sort < cte.Sort)

SET @num = 0;

SELECT tab.*
FROM tab 
INNER JOIN (SELECT (@num := @num   1) AS rn, 
                   t2.Sort
            FROM      tab t1
            LEFT JOIN tab t2 
                   ON t1.ID = t2.ID-1 
            WHERE t1.Sort <> t2.Sort-1) cte
        ON tab.Sort < cte.Sort
       AND cte.rn = <your preferred number>

Check the demo here.