<

A SQLite Tidbit: Quick Numbers Table Generation

Published on
16,020 Points
6,420 Views
1 Endorsement
Last Modified:
Awarded
Community Pick
Kevin Cross
Father, husband and general problem solver who loves coding SQL, C#, Salesforce Apex or whatever.

Introduction

If you are not already aware of what you could use a table with sequential integer values for in SQL, you can read Delimited String Parsing in SQL Server 2005 or later by BrandonGalderisi or Fun with MS SQL spt_values for delimited strings and virtual calendars by mark_wills. Both the numbers view and spt_values in T-SQL could be used in cases where a full table of dates (one row for each day) is needed (e.g., see my Day of the n-th Day in a Month article).

These kinds of needs tend to be SQL platform independent; therefore, the only thing that really is different is what options you have in a particular system to quickly generate a sequence or to create a permanent numbers table that is indexed. Subsequently, this article is to show a neat trick SQLite users (tested in SQLite3 but approach should be version agnostic) can use to generate a numbers table for use in their SQLite database(s).

For those just wanting the goods, here we go ... explanation will follow.

Utility: Numbers Table

-- clear old numbers structure (careful here)
drop table if exists numbers;
-- create numbers utility structure, very basic integer primary key column n
create table numbers(n integer not null, primary key(n asc));
-- insert generated sequential integers
insert into numbers(n)
select rowid 
from (

-- build 1,000,000 rows using Cartesian product
select 1
from (
   select 0 union select 1 union select 2 union select 3 
   union select 4 union select 5 union select 6 
   union select 7 union select 8 union select 9
) a, (
   select 0 union select 1 union select 2 union select 3 
   union select 4 union select 5 union select 6 
   union select 7 union select 8 union select 9
) b, (
   select 0 union select 1 union select 2 union select 3 
   union select 4 union select 5 union select 6 
   union select 7 union select 8 union select 9
) c, (
   select 0 union select 1 union select 2 union select 3 
   union select 4 union select 5 union select 6 
   union select 7 union select 8 union select 9
) d, (
   select 0 union select 1 union select 2 union select 3 
   union select 4 union select 5 union select 6 
   union select 7 union select 8 union select 9
) e, (
   select 0 union select 1 union select 2 union select 3 
   union select 4 union select 5 union select 6 
   union select 7 union select 8 union select 9
) f

) derived;

Open in new window

Voilà !

Hopefully the above speaks for itself, but here is a look at what it is doing:

The innermost query uses the same principles chapmandew applied in his Quickly Generate a Number Sequence in SQL Server article which is a cross between Mark's article on the undocumented master..spt_values in MS SQL and Brandon's write up on creating numbers utility: Cartesian product. Not having a table like spt_values (afaik) in SQLite nor recursive common table expressions at my disposal in concert with my aversion to writing out 1,000 union selects, 6 derived tables of 10 rows each are cross joined. If you have a table in your system with a small series of numbers, that can be used here instead, but this works and performance not really an issue since doing this one time.
Using the Cartesian product as a derived table itself, the outer select utilizes a special 64-bit signed integer column rowid available to all tables in SQLite. When selecting from a standard table, you can simply "select rowid from your_table_name", but here we had to use a derived table since our original query wasn't just a simple select but instead a cross join of multiple tables.

Reusing Numbers Utility

This is a quick trick to implement, but, as you may have read in Brandon's article, there are many good reasons, like reducing overall size of database(s), to having just on copy of our numbers table and other utilities in a separate database we can call util. Then we can use our numbers table in other databases by querying our util database.

Consequently, SQLite allows querying intra databases through attaching.
attach database '{file name}' as {database-name};

Open in new window

The single quotes are not required if your file name doesn't include an extension, but as a convention I have created mine as {database-name}.db so quotes are needed in my example.
attach database 'util.db' as util;

Open in new window

Note that the {database-name} here can be however you want to reference the attached database and doesn't restrict you to having to be the same name as the file. A good example of this is that the primary database connection has a name of 'main' by default. To take a look at the attached databases, you would use the following command:
.databases

Open in new window

Aside from being a useful reminder of the schema name given to your database file(s) you have attached or double-checking a given database is attached before you query, it may be a necessary exercise if you already have connections equal to SQLITE_MAX_ATTACHED. If you need to detach a database, you would use:
detach database  {database-name};

Open in new window

Where {database-name} is the same schema name you assigned during attachment which you will see displayed as name when viewing the results of .databases.

Note: using the command line is easy, but if you are a Mozilla FireFox user, then you may find the SQLite Manager FireFox Add-on useful.
Once attached, we can now take advantage of our numbers table and for that matter any utilities we have stored in util. As test this worked and example of syntax:
select n from util.numbers limit 10;

Open in new window


Numbers Utility In Action

If you are still not convinced this can be used in the real world of SQLite since the referenced articles are T-SQL oriented, here is an example of the numbers table in action when mindwarpltd asked the question: Can I create a loop in an sqlite query, maybe loop is the wrong word...

The request:
I want to produce a list of recurring items from a table where one record exists.

e.g. keep adding records which accumulate and increase the due date

The pertinent piece of the ultimate solution:
select a.ID, a.desc
     , date(a.startdate, '+'||(b.n*a.interval)||' '||a.intervaltype) as due
     , a.[price]
from billsndeposits a
inner join util_nums b -- <<<< this would be util.numbers
   on date(a.startdate, '+'||(b.n*a.interval)||' '||a.intervaltype) < date('2011-01-01')
where not (intervaltype = 'once' or interval = 0)
order by due, id;

Open in new window

Using the date() function, this simulates a loop by replicating rows of the bills and deposits table by a number (n) from our numbers table that when added to the start date in a specific interval of time resulted in a date that fell within a given time period. This select was then used to create a new table, but as you can see was useful in putting into a single statement what would have normally been a multi-line statement with a loop.

Note: At the time of writing this, SQLite doesn't seem to have any loop capabilities outside of scripting using shell or other languages capability of using its C-based libraries. Therefore, this is a bigger advantage as it allows what may not otherwise be possible in pure SQLite query syntax.
In the question, I used a quick and dirty approach to creating the util_nums table.  The technique used there is another option for you. It involved creating the table with an INTEGER PRIMARY KEY AUTOINCREMENT column and then using the same Cartesian product query but selecting null which, when inserted will result in a default, incremented, value in the primary key column.

Conclusion

We now have a nice quick way to create a useful numbers utility in SQLite. In addition, we have been introduced to rowid | _rowid_ | oid which can be useful for other things in its own right. Think if we had a table with 1,000 rows already in our database we could have simply cross joined two queries that each selected rowid from our table to get our 1,000,000 rows.

Enjoy...

Thanks for reading!

Best regards and happy coding,

Kevin C. Cross, Sr. (mwvisa1)

References

Related Articles
http://sqlservernation.com/blogs/tipweek/archive/2009/04/27/the-numbers-table-round-2.aspx
http://sqlservernation.com/blogs/tipweek/archive/2010/06/27/quickly-generate-a-number-sequence-in-sql-server.aspx
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_1221-Fun-with-MS-SQL-spt-values-for-delimited-strings-and-virtual-calendars.html

Related Q&A
http://www.experts-exchange.com/Database/Miscellaneous/Q_26397603.html

Related SQLite Documentation
http://www.sqlite.org/autoinc.html
http://www.sqlite.org/lang_createtable.html#rowid

http://www.sqlite.org/lang_attach.html
http://www.sqlite.org/lang_detach.html
http://www.sqlite.org/limits.html#max_attached

Resources
https://addons.mozilla.org/en-US/firefox/addon/5817/

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
If you found this article helpful, please click the Yes button after the question just below. This will give me (the author) a few points and might encourage me to write more articles.

If you didn't or otherwise feel the need to vote No, please first leave a comment to give me a chance to answer and perhaps to improve this article.

Thank you!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
1
Comment
Author:Kevin Cross
0 Comments

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Join & Write a Comment

Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month