sqlmariadb-10.1

How to substitute “with” statement in mariadb 10.1

I use a third party software with MariaDB 10.1 How can I subsitute sql "with" statement (not available with MariaDB 10.1) ?

I would like to do something like that :

WITH mCte
AS (
  SELECT 
     @row_num: = @row_num + 1 AS Cpt
    ,field1
  FROM (
    SELECT @row_num: = 0 AS row_num
      ,myField AS field1
    FROM myTable
    ) AS T
  )
SELECT *
FROM mCte
WHERE Cpt BETWEEN 1 AND 5

Thanks

Solution

  • I would suggest:

    select @row_num := @row_num + 1 AS Cpt, field1
from myTable t cross join
     (select @row_num := 0 as row_num) params
limit 5;

    Normally, I would expect an order by in the query, but your code doesn't have one.