CODEWITHSUNDEEP
mysqlsql
bodruk
bodruk

Reputation: 3262

MySQL: Select from list of values

I was wondering if I could select given values from a list and populate rows? For example, SELECT 1 as one, 2 as two, 3 as three will populate columns:

one    | two    | three
------------------------
1      | 2      | 3

I'm looking for a script that populates rows, something like:

values
-------
1
2
3
4

Thanks!

Upvotes: 13

Views: 28112

Answers (2)

spencer7593
spencer7593

Reputation: 108370

To get a few numbers, the approach from John Ruddell is the probably the most convenient, I can easily incorporate an inline view in any query I'm needing to run.

When I need a lot of numbers, for example, 1 through 4000, I can do something like this:

CREATE TABLE digit (d INT(11) NOT NULL PRIMARY KEY);
INSERT INTO digit (d) VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

SELECT thousands.d*1000+hundreds.d*100+tens.d*10+ones.d+1 AS n
  FROM digit ones
 CROSS
  JOIN digit tens
 CROSS
  JOIN digit hundreds
 CROSS
  JOIN digit thousands
 WHERE thousands.d < 4

I can also add a HAVING clause if the boundaries of the numbers I need aren't quite as neat, e.g

HAVING n >= 121
   AND n <= 2499

If I want to ensure the "numbers" are returned in order, I'll add an ORDER BY clause:

ORDER BY n

Upvotes: 4

John Ruddell
John Ruddell

Reputation: 25842

you can union each one if you want like so

SELECT 1 AS numbers
UNION SELECT 2
UNION SELECT 3

a much simpler way to do something like this would be to make a table with an auto incremented id... insert into another column in the table an empty string... then just select the auto incremented id

CREATE TEMPORARY TABLE tmp (
    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    val  varchar(1) 
);
INSERT INTO tmp (val)
values
(""),
(""),
(""),
(""),
(""),
(""),
(""),
(""),
(""),
("");
select id from tmp;

DEMO

Upvotes: 18

Related Questions