How do I get a list of numbers in MySQL?

Question

I've got a database of movies, and I'd like a list of years where I don't have a movie for that year. So all I need is a list (1900 .. 2012) and then I can JOIN and IN and NOT IN on that all I want.

I've got:

CREATE PROCEDURE build_years(p1 SMALLINT) 
BEGIN 
    CREATE TEMPORARY TABLE year (year SMALLINT(5) UNSIGNED); 
    label1: LOOP 
        INSERT INTO year VALUES (p1); 
        SET p1 = p1 + 1; 
        IF p1 > 2012 THEN LEAVE label1; END IF; 
    END LOOP; 
END

But that seems so unSQL and only marginally less kludgy then running Python code to create the same table. I'd really like something that didn't use a stored procedure, didn't use looping and didn't use an actual table, in that order of concern.

Why do you not want to use an actual table? Seems like it would be useful if you are going to run this query more than once? — GarethD, Jun 06 '12 at 21:09
I was really hoping there was some direct code I had overlooked that I could use inline for general purposes. It just feels like everytime I create a new temporary table in SQL, I've missed the elegant solution. Especially here where I'm creating a permanent table to replace Python's range(1900, 2013). — prosfilaes, Jun 06 '12 at 21:49

score 4 · Answer 1 · answered Jun 06 '12 at 22:10

This should work until you need more than 195 years , at which point you'll need to add a UNION ALL:

SELECT Year 
FROM   (   SELECT @i:= @i + 1 AS YEAR
           FROM   INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY,
                  ( SELECT @i:= 1899) AS i
        ) As Y
WHERE   Year BETWEEN 1900 AND 2012
ORDER BY Year;

Although I am assuming that the COLLATION_CHARACTER_SET_APPLICABILITY System table has a default size of 195 based on my trusty testing ground SQL Fiddle

score 3 · Accepted Answer · answered Jun 06 '12 at 21:22

I had similar problem a few years ago. My solution was:

1. Sequence table

I created a table filled with integer sequence from 0 to < as much as it will be required >:

CREATE TABLE numbers (n INT);
INSERT INTO numbers VALUES (0),(1),(2),(3),(4);
INSERT INTO numbers SELECT n+5 FROM numbers;
INSERT INTO numbers SELECT n+10 FROM numbers;
INSERT INTO numbers SELECT n+20 FROM numbers;
INSERT INTO numbers SELECT n+40 FROM numbers;
etc.

It is executed only once, so can be created from outside of your app, even by hand.

2. Select data of a needed type and range

For integers it is obvious - i.e. range 1..99:

SELECT n FROM numbers WHERE n BETWEEN 1 AND 99;

Dates - 2h intervals from now to +2 days:

SELECT date_add(now(),INTERVAL 2*n HOUR) FROM numbers WHERE n BETWEEN 0 AND 23;

So in your case it could be:

SELECT n+1900 AS n_year FROM numbers WHERE n BETWEEN 0 AND 112;

Then JOIN it on n_year.

This answer isn't necessarily exactly what I was requesting, but I does seem to be the cleanest, most practical and useful solution to the problem. Thanks also to the other people who responded with answers that fulfilled the requirements, even if I found them less practical. — prosfilaes, Jun 06 '12 at 21:54

score 1 · Answer 3 · answered Jun 06 '12 at 21:23

1

This will return a list of 2012 to 1900 if you really want to keep it to a query..

SELECT 
    TO_CHAR (ADD_MONTHS (TRUNC (SYSDATE, 'YYYY'), ((rno - 1) * -12)), 'YYYY') AS "years"
FROM 
    (
    SELECT 
        LEVEL rno
    FROM DUAL
    CONNECT BY LEVEL <=
                   (SELECT TO_CHAR (TRUNC (SYSDATE, 'YYYY'), 'YYYY')
                           - 1899
                               yearstobuild
                      FROM DUAL))

answered Jun 06 '12 at 21:23

John D

2,307
17
28

This would be a better answer if the the question was tagged with `oracle` and not `MySQL`. – GarethD Jun 06 '12 at 21:39
1

Argh disaster - completely missed that! – John D Jun 06 '12 at 21:40

score 0 · Answer 4 · answered Jun 06 '12 at 21:21

The only solution I can think of according to your wishes sucks also ...

SELECT years.year FROM
    (
        SELECT 1900 AS year
        UNION SELECT 1901
        ...
        UNION SELECT 2012
    ) AS years
    LEFT OUTER JOIN yourmovietable USING (year)
WHERE yourmovietable.year IS NULL;

score 0 · Answer 5 · answered Nov 20 '17 at 13:08

Using this generic query is faster:

INSERT INTO numbers SELECT n+(SELECT COUNT(*) FROM numbers) FROM numbers;

Each query execution duplicates:

INSERT INTO numbers VALUES (0),(1),(2),(3),(4);

INSERT INTO numbers SELECT n+(SELECT COUNT(*) FROM numbers) FROM numbers;
INSERT INTO numbers SELECT n+(SELECT COUNT(*) FROM numbers) FROM numbers;
INSERT INTO numbers SELECT n+(SELECT COUNT(*) FROM numbers) FROM numbers;

...

Markus Mikkolainen · Answer 6 · 2012-06-06T21:09:58.873

-1

select year into temporary table blaa from (generate_series(1900,2000)) where year not in(select distinct(year) from films)

dont know if this will work but you get the drift.

edited Jun 06 '12 at 21:09

answered Jun 06 '12 at 20:52

Markus Mikkolainen

3,397
18
21

2

He's not asking how to query his films table (I assume he already knows how to do that). He's asking how to create the year table in a cleaner way. Your query won't work because there won't be any rows with `c = 0`. – Mark Byers Jun 06 '12 at 20:53
and you can do "create table as" – Markus Mikkolainen Jun 06 '12 at 20:59
good point that it wont work. Then you would have to maybe use a function like generate_series in postgresql to generate a series of years. – Markus Mikkolainen Jun 06 '12 at 21:08
1

Yes, something like `generate_series` would be useful here. Unfortunately MySQL has no such function. :-( – Mark Byers Jun 06 '12 at 21:09

How do I get a list of numbers in MySQL?

6 Answers6

Linked