All Questions
13,129
questions
1730votes
20answers
1.5mviews
Select first row in each GROUP BY group?
As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY.
Specifically, if I've got a purchases table that looks like this:
SELECT * FROM purchases;
My ...
- 138k
1298votes
17answers
1.1mviews
How can I list the tables in a SQLite database file that was opened with ATTACH?
What SQL can be used to list the tables, and the rows within those tables in an SQLite database file - once I have attached it with the ATTACH command on the SQLite 3 command line tool?
- 47.5k
610votes
25answers
439kviews
Is it possible to insert multiple rows at a time in an SQLite database?
In MySQL you can insert multiple rows like this:
INSERT INTO 'tablename' ('column1', 'column2') VALUES
('data1', 'data2'),
('data1', 'data2'),
('data1', 'data2'),
('data1', 'data2');
...
- 210k
590votes
19answers
340kviews
SQLite - UPSERT *not* INSERT or REPLACE
http://en.wikipedia.org/wiki/Upsert
Insert Update stored proc on SQL Server
Is there some clever way to do this in SQLite that I have not thought of?
Basically I want to update three out of four ...
- 8,224
239votes
9answers
204kviews
Best way to work with dates in Android SQLite [closed]
I'm having some trouble working with dates on my Android application that uses SQLite.
I have a couple questions:
What type should I use to store dates in SQLite (text, integer, ...)?
Given the best ...
- 3,348
211votes
9answers
230kviews
sqlite database default time value 'now'
Is it possible in a sqlite database to craete a table that has a timestamp column that default to DATETIME('now') ?
Like this:
CREATE TABLE test (
id INTEGER PRIMARY KEY AUTOINCREMENT,
t ...
- 3,773
197votes
13answers
235kviews
How do I dump the data of some SQLite3 tables?
How do I dump the data, and only the data, not the schema, of some SQLite3 tables of a database (not all the tables)?
The dump should be in SQL format, as it should be easily re-entered into the ...
181votes
4answers
77kviews
sqlite alter table add MULTIPLE columns in a single statement
Is it possible to alter table add MULTIPLE columns in a single statement in sqlite?
The following would not work.
alter table test add column mycolumn1 text, add column mycolumn2 text;
- 19.9k
164votes
12answers
151kviews
How do I add a foreign key to an existing SQLite table?
I have the following table:
CREATE TABLE child(
id INTEGER PRIMARY KEY,
parent_id INTEGER,
description TEXT);
How do I add a foreign key constraint on parent_id? Assume foreign keys are ...
- 71k
154votes
16answers
139kviews
How can I get dict from sqlite query?
db = sqlite.connect("test.sqlite")
res = db.execute("select * from table")
With iteration I get lists coresponding to the rows.
for row in res:
print row
I can get name of the columns
...
- 11.9k
153votes
20answers
382kviews
SQLite in Android How to update a specific row
I've been trying to update a specific row for a while now, and it seems that there are two ways to do this. From what I've read and tried, you can just use the:
execSQL(String sql) method
or the:
...
- 17.2k
152votes
3answers
115kviews
How to use SQL Order By statement to sort results case insensitive?
I have a SQLite database that I am trying to sort by Alphabetical order. The problem is, SQLite doesn't seem to consider A=a during sorting, thus I get results like this:
A
B
C
T
a
b
c
g
I want to ...
- 14.5k
142votes
4answers
92kviews
String concatenation does not work in SQLite
I am trying to execute a SQlite replace function, but use another field in the function.
select locationname + '<p>' from location;
In this snip, the result is a list of 0s. I would have ...
- 63.3k
141votes
5answers
120kviews
SQLite Reset Primary Key Field
I have a few tables in SQLite and I am trying to figure out how to reset the auto-incremented database field.
I read that DELETE FROM tablename should delete everything and reset the auto-...
- 4,739
140votes
11answers
209kviews
Sqlite: CURRENT_TIMESTAMP is in GMT, not the timezone of the machine
I have a sqlite (v3) table with this column definition:
"timestamp" DATETIME DEFAULT CURRENT_TIMESTAMP
The server that this database lives on is in the CST time zone. When I insert into my table ...
- 7,571