0

In result set i should get something like the integer * by 3 , to certain limit say ex till 20, ie. till 20*3 =60 In mysql 5.X using just Sql

col1
3
6
9
12
15
..
..
..
..

In oracle we can easily do by using level clause.

JagaSrik
  • 700
  • 7
  • 23
  • Edited the question i want to know how to do it with mysql 5.x as in mysql 8.X i can easily do it using recursive with clause. – JagaSrik Jul 10 '20 at 06:28
  • 1
    I'd recommend you to create static service `numbers` table in your database (or in some system database for to use with any database) with the numbers which is enough with guarantee (for example, from 1 to 1000) and select needed numbers from it (for example, `SELECT number FROM mysql.numbers where !MOD(number, 3) AND (number < 100);`). *In result set i should get* Do you need this in subquery form or in (temporary) table static form? – Akina Jul 10 '20 at 06:32
  • I like to get in subquery form for example in oracle i can do it by this query select level*3 from dual connect by level <15; something similar, i tried set @n=1; select @n*3 from dual; set @n=@n+1; this sequence but it dont work as loop is missing here, but i dont want to do in procedure, before going for procedure i want to confirm there is no way we can do it in just sql. – JagaSrik Jul 10 '20 at 06:40
  • @Strawberry i understand it and checked the article, please let me know if something is violated. Will correct it if it is. – JagaSrik Jul 10 '20 at 06:46
  • In essence, you can regard my previous comment as a notification to that effect – Strawberry Jul 10 '20 at 06:49
  • @Strawberry sure, thank you. – JagaSrik Jul 10 '20 at 06:52

3 Answers3

2

In MySQL you can create a procedure to store the result in a temporary table and display it afterwards, something like this:

DELIMITER $
CREATE PROCEDURE `temp`(upto integer)
BEGIN
    DROP TABLE IF EXISTS multiple;
    CREATE TABLE multiple(col1 integer);
    SET @i := 1;
    while @i <= upto DO
        set @val := 3 * @i;
        set @sql := CONCAT('INSERT INTO multiple values(',@val,')');
        prepare b from @sql;
        execute b;
        set @i := @i + 1;
    end while;

    select * from multiple;
end $

DELIMITER ;

call temp(20);
CaffeinatedCod3r
  • 821
  • 7
  • 14
  • I should have specified this, i want to know how to do it with just sql. Thanks this works but i want to know this in just sql. – JagaSrik Jul 10 '20 at 06:31
  • @Jfrd Do you need this column along with another result set? like as an exra column or just this column? – CaffeinatedCod3r Jul 10 '20 at 06:37
  • just this column.. i think it will make a good interview question – JagaSrik Jul 10 '20 at 06:48
  • 1
    @Jfrd i dont think its possible to do it in pure sql query in MySQL 5.One workaround is you can create a table with all integers upto 100000.Then select from it by limit clause. – CaffeinatedCod3r Jul 10 '20 at 06:57
  • 1
    @Jfrd, you might need any table with many rows to do this. Without rows in mysql 5 this can't be achieved. – Helper Jul 10 '20 at 07:04
  • there is a way i found it from another post pretty creative i will post it as answer – JagaSrik Jul 10 '20 at 07:38
1

CONNECT BY LEVEL is Oracle's old propriatary syntax for recursive queries.

The SQL standard uses recursive CTEs instead, supported by Oracle as of version 11.2 and by MySQL as of version 8.

with recursive numbers(number) as
(
  select 3
  union all
  select number + 3 from numbers where number + 3 <= 20
)
select number
from numbers
order by number;

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b497c18f4632d4a7fe77a6a0027a08e6

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • Thorsten, I wanted to check with mysql 5.X , mysql 5 dont have recursive facility. – JagaSrik Jul 13 '20 at 13:21
  • No, it doesn't. In MySQL it makes a huge difference whether to work with version 8.0 or with an older version. If you can, you should uprade. And as the version is so important with MySQL, you should always tag your MySQL requests with the version you are using, e.g. `mysql-5.7`, so people know what features your DBMS has and what features it lacks. – Thorsten Kettner Jul 13 '20 at 13:41
  • it was asked in one interview so i wanted to check if its possible, i have mentioned version in the body but i will keep in mind to put version tag in my futures question also will edit this question to tag the same, luckily i found something that can work with msql 5.X here on SO i have posted it as answer. – JagaSrik Jul 13 '20 at 13:47
0

I found this answer from the post https://stackoverflow.com/a/187410/9010859 and modified it to suit this requirement.

select SeqValue*3 from (SELECT
    (HUNDREDS.SeqValue + TENS.SeqValue + ONES.SeqValue) SeqValue
FROM
    (
    SELECT 0  SeqValue
    UNION ALL
    SELECT 1 SeqValue
    UNION ALL
    SELECT 2 SeqValue
    UNION ALL
    SELECT 3 SeqValue
    UNION ALL
    SELECT 4 SeqValue
    UNION ALL
    SELECT 5 SeqValue
    UNION ALL
    SELECT 6 SeqValue
    UNION ALL
    SELECT 7 SeqValue
    UNION ALL
    SELECT 8 SeqValue
    UNION ALL
    SELECT 9 SeqValue
    ) ONES
CROSS JOIN
    (
    SELECT 0 SeqValue
    UNION ALL
    SELECT 10 SeqValue
    UNION ALL
    SELECT 20 SeqValue
    UNION ALL
    SELECT 30 SeqValue
    UNION ALL
    SELECT 40 SeqValue
    UNION ALL
    SELECT 50 SeqValue
    UNION ALL
    SELECT 60 SeqValue
    UNION ALL
    SELECT 70 SeqValue
    UNION ALL
    SELECT 80 SeqValue
    UNION ALL
    SELECT 90 SeqValue
    ) TENS
CROSS JOIN
    (
    SELECT 0 SeqValue
    UNION ALL
    SELECT 100 SeqValue
    UNION ALL
    SELECT 200 SeqValue
    UNION ALL
    SELECT 300 SeqValue
    UNION ALL
    SELECT 400 SeqValue
    UNION ALL
    SELECT 500 SeqValue
    UNION ALL
    SELECT 600 SeqValue
    UNION ALL
    SELECT 700 SeqValue
    UNION ALL
    SELECT 800 SeqValue
    UNION ALL
    SELECT 900 SeqValue
    ) HUNDREDS) as a where seqValue != 0 limit 20;
JagaSrik
  • 700
  • 7
  • 23