I know some of the solutions in SQL but couldn't find any of them from SQlite.
I just want to execute a select query that returns a resultset of numbers ranging from 1 to 100.
Numbers
1
2
3
4
......
5
A correction: I don't actually have a table at all. (however a solution is encouraged with a virtual table like dual in MySQL)
Thanks sgmentzer! Inspired by your answer I went ahead and also found this:
WITH RECURSIVE
cnt(x) AS (
SELECT 1
UNION ALL
SELECT x+1 FROM cnt
LIMIT 100000
)
SELECT x FROM cnt;
How about
SELECT * FROM myTable WHERE myNumber >= 1 AND myNumber <= 100;
?
Example subquery to generate the series 1 <= n <= 100000 in SQLite. No table is created or used.
select 1+e+d*10+c*100+b*1000+a*10000 as n from
(select 0 as a 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),
(select 0 as b 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),
(select 0 as c 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),
(select 0 as d 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),
(select 0 as e 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);
I don't think SQLite has a clean way to do this, so you'll need to use a virtual table interface. SQLite ships one for 'C', and apsw has one for python as I'll demonstrate below. Here's documentation for the APSW Virtual Table interface.
#!/usr/bin/python
import apsw,tempfile
### Opening/creating database
filename=tempfile.mktemp() #insecure - do not use in production code
connection=apsw.Connection(filename)
cursor=connection.cursor()
# This gets registered with the Connection
class Source:
def Create(self, db, modulename, dbname, tablename, *args):
schema="create table foo( dummy integer )"
return schema,Table()
Connect=Create
# Represents a table
class Table:
def __init__(self):
pass
def BestIndex(self, constraints, orderbys):
used = []
self.constraints = []
ucount = 0
for c in constraints:
if c[1] in (
apsw.SQLITE_INDEX_CONSTRAINT_GT,
apsw.SQLITE_INDEX_CONSTRAINT_GE,
apsw.SQLITE_INDEX_CONSTRAINT_LT,
apsw.SQLITE_INDEX_CONSTRAINT_LE,
apsw.SQLITE_INDEX_CONSTRAINT_EQ,
):
used.append( ucount ) #tell sqlite we want to use this one
self.constraints.append( c[1] ) #save some for later
else:
used.append( None ) #skip anything we don't understand
ucount += 1
return ( used, # used constraints list
0, # index number - no biggie we only support one right now
)
def Open(self):
return Cursor(self)
def Disconnect(self):
pass
Destroy=Disconnect
# Represents a cursor
class Cursor:
def __init__(self, table):
self.table=table
def Filter(self, indexnum, indexname, constraintargs):
start = 0
self.end = 4000000000
#map constraint arguments to start and end of generation
for tc, ca in zip( self.table.constraints, constraintargs ):
if tc == apsw.SQLITE_INDEX_CONSTRAINT_EQ:
start = ca
self.end = ca
elif tc == apsw.SQLITE_INDEX_CONSTRAINT_LE:
if self.end > ca:
self.end = ca
elif tc == apsw.SQLITE_INDEX_CONSTRAINT_LT:
if self.end >= ca:
self.end = ca
elif tc == apsw.SQLITE_INDEX_CONSTRAINT_GE:
if start < ca:
start = ca
elif tc == apsw.SQLITE_INDEX_CONSTRAINT_GT:
if start >= ca:
start = ca
self.pos = start
def Eof(self):
return self.pos > self.end
def Rowid(self):
return self.pos
def Next(self):
self.pos+=1
def Close(self):
pass
# Register the module as intsource, you can make a bunch if needed
connection.createmodule("intsource", Source())
# Create virtual table to use intsource
cursor.execute("create virtual table uints using intsource()")
# Do some counting
for i in cursor.execute("SELECT rowid FROM uints WHERE rowid BETWEEN 1 AND 100"):
print i
This implements a virtual-table type named "intsource", which by default counts from 0 to 4*10^9. It supports directly filtering by equality and comparison, but any other constraints will still be filtered out by sqlite. Virtual tables are a very powerful concept you can do a lot with, this is probably one of the simplest uses for them. Also, thank you for a good excuse to try out a new virtual table API.
SELECT * FROM myTable WHERE myNumber BETWEEN 1 AND 100;
This is more efficient than using 2 WHERE clauses.
If your goal is to select actual records from a table with values between 1 and 100, use BETWEEN as shown by the other respondents.
If your goal is to generate a sequence of numbers from 1 to 100 without having a table to base it on, I don't believe SQLite has a feature that does this.
