Re: [sqlite] Is this normal behavior for a unique index?
Never mind Simon. It turns out that SQLite allows NULLable fields to participate in UNIQUE indexes. I'd screwed up the code I'd made to replace anything undefined with "". I've changed it, and it's properly saving empty strings now, and the index is now successfully ignoring identical row insert attempts. Thanks mate. On Fri, Mar 28, 2014 at 12:42 PM, Simon Slavin wrote: > > On 28 Mar 2014, at 1:12am, SongbookDB > wrote: > > > However, if, for example the DiscId is blank, but the artist and title > are > > not, a new record is created when there is already one with a blank > DiscId > > and the same artist and title - BAD. > > What's the primary key on that TABLE ? Include both the column name and > the column's affiliation. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Shaun Thomson Owner - SongbookDB ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this normal behavior for a unique index?
Gidday Simon The PK is the first column, an id, created as PalID INTEGER PRIMARY KEY AUTOINCREMENT Not sure what you mean by affiliation, but if you mean related indexes, there are none 0 the only index on the table is CREATE UNIQUE INDEX songsIndex ON songs ( DiscID, Artist, Title ) Thanks mate On Fri, Mar 28, 2014 at 12:42 PM, Simon Slavin wrote: > > On 28 Mar 2014, at 1:12am, SongbookDB > wrote: > > > However, if, for example the DiscId is blank, but the artist and title > are > > not, a new record is created when there is already one with a blank > DiscId > > and the same artist and title - BAD. > > What's the primary key on that TABLE ? Include both the column name and > the column's affiliation. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Shaun Thomson Owner - SongbookDB ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is this normal behavior for a unique index?
Gidday guys I'm working with SQLite in Flash. I have this unique index: CREATE UNIQUE INDEX songsIndex ON songs ( DiscID, Artist, Title ) I have a parametised recursive functionset up to insert any new rows (single or multiple). It works fine if I try to insert a row with the same DiscID, Artist and Title as an existing row - ie it ignores inserting the existing row, and tells me that 0 out of 1 records were updated - GOOD. However, if, for example the DiscId is blank, but the artist and title are not, a new record is created when there is already one with a blank DiscId and the same artist and title - BAD. I traced out the disc id prior to the insert, and Flash is telling me it's undefined. So I've coded it to set anything undefined to "" (an empty string) to make sure it's truly an empty string being inserted - but subsequent inserts still ignore the unique index and add a brand new row even though the same row exists. What am I misunderstanding? Thanks for your time and help. -- Shaun Thomson Owner - SongbookDB ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UNIQUE index not working as expected - what am I overlooking?
Gidday guys I'm working with SQLite in Flash. I have this unique index: CREATE UNIQUE INDEX songsIndex ON songs ( DiscID, Artist, Title ) I have a parametised recursive functionset up to insert any new rows (single or multiple). It works fine if I try to insert a row with the same DiscID, Artist and Title as an existing row - ie it ignores inserting the existing row, and tells me that 0 out of 1 records were updated - GOOD. However, if, for example the DiscId is blank, but the artist and title are not, a new record is created when there is already one with a blank DiscId and the same artist and title - BAD. I traced out the disc id prior to the insert, and Flash is telling me it's undefined. So I've coded it to set anything undefined to "" (an empty string) to make sure it's truly an empty string being inserted - but subsequent inserts still ignore the unique index and add a brand new row even though the same row exists. What am I misunderstanding? Thanks for your time and help. -- Shaun Thomson Owner - SongbookDB ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ghost index?
Thanks Kees - I managed to get it working. On Tue, Nov 12, 2013 at 11:49 PM, Kees Nuyt wrote: > On Tue, 12 Nov 2013 15:00:36 +1100, SongbookDB > wrote: > > >Ahh - ok. I was typing sqlite3 db.3sdb IN sqlite3, not command prompt. > > > >I can load and search the db now. > > > >So to drop the index 'index1' for the table 'table1', do I just type DROP > >INDEX IF EXISTS 'index1'; ? Doing so still shows the index when I load the > >database in SQLite Administrator. > > What operating system are we talking about? > > What is the exact full path to the database file? > > -- > Groet, Cordialement, Pozdrawiam, Regards, > > Kees Nuyt > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Shaun Thomson Owner - SongbookDB ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Where to look for SQLite coder?
Hi - I need a small amount of coding done, and am wondering if someone can recommend a good place to post for an SQLite coder? Cheers ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ghost index?
I've notices if I make a mistake, the prompt turns to ...>, and then legitimate commands no longer work. Is there a way to get back to the normal prompt? On Tue, Nov 12, 2013 at 2:42 PM, Simon Slavin wrote: > > On 12 Nov 2013, at 2:49am, SongbookDB > wrote: > > > I've named it db.s3db, run the shell, type sqlite3 db, then select * from > > songs; and it gives the error "Error: near "sqlite3": syntax error. > > as a command to your operating system > > sqlite3 db.s3db > > then once you're in the program type SQL commands or things like > > .schema > SELECT * FROM sqlite_master; > DROP INDEX fred; > SELECT * FROM sqlite_master; > .quit > > .quit to exit. For further information > > <http://www.sqlite.org/sqlite.html> > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Shaun Thomson Owner - SongbookDB ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Never mind Simon - Re: Ghost index?
Please disregard my last email - I was loading an incorrect copy of the database into SQLite Administrator. On Tue, Nov 12, 2013 at 2:42 PM, Simon Slavin wrote: > > On 12 Nov 2013, at 2:49am, SongbookDB > wrote: > > > I've named it db.s3db, run the shell, type sqlite3 db, then select * from > > songs; and it gives the error "Error: near "sqlite3": syntax error. > > as a command to your operating system > > sqlite3 db.s3db > > then once you're in the program type SQL commands or things like > > .schema > SELECT * FROM sqlite_master; > DROP INDEX fred; > SELECT * FROM sqlite_master; > .quit > > .quit to exit. For further information > > <http://www.sqlite.org/sqlite.html> > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Shaun Thomson Owner - SongbookDB ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ghost index?
Ahh - ok. I was typing sqlite3 db.3sdb IN sqlite3, not command prompt. I can load and search the db now. So to drop the index 'index1' for the table 'table1', do I just type DROP INDEX IF EXISTS 'index1'; ? Doing so still shows the index when I load the database in SQLite Administrator. On Tue, Nov 12, 2013 at 2:42 PM, Simon Slavin wrote: > > On 12 Nov 2013, at 2:49am, SongbookDB > wrote: > > > I've named it db.s3db, run the shell, type sqlite3 db, then select * from > > songs; and it gives the error "Error: near "sqlite3": syntax error. > > as a command to your operating system > > sqlite3 db.s3db > > then once you're in the program type SQL commands or things like > > .schema > SELECT * FROM sqlite_master; > DROP INDEX fred; > SELECT * FROM sqlite_master; > .quit > > .quit to exit. For further information > > <http://www.sqlite.org/sqlite.html> > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Shaun Thomson Owner - SongbookDB ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ghost index?
I've named it db.s3db, run the shell, type sqlite3 db, then select * from songs; and it gives the error "Error: near "sqlite3": syntax error. On Tue, Nov 12, 2013 at 1:26 PM, Simon Slavin wrote: > > On 12 Nov 2013, at 1:50am, SongbookDB > wrote: > > > I've downloaded and run the shell tool - re opening a database, where do > > base dir paths start from? I have the sqlite3.exe in a dir called test in > > my downloads folder, and the test database is called db.sbdb - typing > > sqlite3 db.sbdb doesn't open it. > > Paths start from your current directory. The one you'd see if you typed > DIR or ls. > > Or you could try specifying a full path. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Shaun Thomson Owner - SongbookDB ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ghost index?
Does the shell expect a certain filename extension on the database file? I can't get mine to open. Thanks Simon. On Tue, Nov 12, 2013 at 1:26 PM, Simon Slavin wrote: > > On 12 Nov 2013, at 1:50am, SongbookDB > wrote: > > > I've downloaded and run the shell tool - re opening a database, where do > > base dir paths start from? I have the sqlite3.exe in a dir called test in > > my downloads folder, and the test database is called db.sbdb - typing > > sqlite3 db.sbdb doesn't open it. > > Paths start from your current directory. The one you'd see if you typed > DIR or ls. > > Or you could try specifying a full path. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Shaun Thomson Owner - SongbookDB ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ghost index?
Thanks Simon I'm not sure how to set up logging when using SQLite with as3 AIR - I'll check in with the Adobe community. I've downloaded and run the shell tool - re opening a database, where do base dir paths start from? I have the sqlite3.exe in a dir called test in my downloads folder, and the test database is called db.sbdb - typing sqlite3 db.sbdb doesn't open it. On Tue, Nov 12, 2013 at 12:14 PM, Simon Slavin wrote: > > On 12 Nov 2013, at 1:07am, Simon Slavin wrote: > > > On 12 Nov 2013, at 12:51am, SongbookDB > wrote: > > > >> The process works fine at first - first time around, the index is > dropped > >> (this takes about 20 seconds on my test database). On subsequent loads, > no > >> dropping occurs. > > > > Are you logging the result code returned from your "DROP INDEX" command > ? Does it always return SQLITE_OK ? > > I forgot to mention that 20 seconds for any SQLite command is very > unusual. I suspect database locking, database corruption, or some other > weirdness. Therefore make sure your operations are doing what you think > they're doing. > > You can check what's happening using the SQLite shell tool which you can > download from the SQLite site. Try the DROP INDEX command with that > instead. If it doesn't take 20 seconds then there's something wrong with > your other method. > > Simon. > _______ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Shaun Thomson Owner - SongbookDB ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Ghost index?
Hi I have a desktop app that loads an SQLite database into a datagrid. Before loading data, it goes through series of checks to make sure that the user's database has the correct indexes set up. The very last check is to test for the existence of the index I originally used on the database a while back, and if there, drop it, using "DROP INDEX IF EXISTS index1" - Once dropped, the index should NEVER have to be dropped again, as it's not there, right? The process works fine at first - first time around, the index is dropped (this takes about 20 seconds on my test database). On subsequent loads, no dropping occurs. However, if you restart your computer, the dropping process occurs again the first time the app loads the database!!! I tested for the existence of the index by restarting, and loading the database in SQLite Administrator, and it's definitely NOT there. Any idea what could be causing this? Thanks for taking a look. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Disparity between query time in SQLite Administrator and Flash Pro
Thanks Stephen - had a quick play with SQLite Expert just now before sleep - found a 3.7.4 dll and dropped it in and it loaded up fine. I'll see if the queries take the same time as in Flash tomorrow. On Thu, Nov 7, 2013 at 10:53 PM, Stephen Chrzanowski wrote: > SQLite Expert allows you to use any SQLite DLL. Its free for > non-commercial use, but even that said, its an awesome package to buy, life > time upgrades, and seemingly always being updated. > > http://www.sqliteexpert.com/ > > > > On Wed, Nov 6, 2013 at 7:30 PM, SongbookDB >wrote: > > > Just got a reply re the version Flash uses - it's 3.7.4 RC4, which is > later > > than what SQLite Administrator uses (3.5.1). > > > > Is there a program like SQLite Administrator that will let me load a > > particular version of SQLite, so my testing will match the version in > Flash > > AIR? > > > > FYI - he used this to get the version from AIR - SELECT > sqlite_source_id() > > - and then looked the hash code returned up on the SQLite timeline at > > http://www.sqlite.org/cgi/src/info/a586a4deeb253300 > > > > > > On Thu, Nov 7, 2013 at 11:21 AM, Simon Slavin > > wrote: > > > > > > > > On 7 Nov 2013, at 12:09am, SongbookDB > > > wrote: > > > > > > > The database seems to write fine. I have it loading into a datagrid > in > > my > > > > Flash AIR desktop app, from where I read it and make edits to it. > > > > > > I don't understand how any SQLite app can return the error you gave: > > > > > > >> Flash gives the error - no such function: 'sqlite_version' > > > > > > All APIs that allow you to execute SQLite commands should let you > execute > > > > > > SELECT sqlite_version() > > > > > > so perhaps you have better ask the Flash Pro people about this problem. > > > > > > Simon. > > > ___ > > > sqlite-users mailing list > > > sqlite-users@sqlite.org > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > > -- > > Shaun Thomson > > Owner - SongbookDB > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Shaun Thomson Owner - SongbookDB ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Disparity between query time in SQLite Administrator and Flash Pro
Just got a reply re the version Flash uses - it's 3.7.4 RC4, which is later than what SQLite Administrator uses (3.5.1). Is there a program like SQLite Administrator that will let me load a particular version of SQLite, so my testing will match the version in Flash AIR? FYI - he used this to get the version from AIR - SELECT sqlite_source_id() - and then looked the hash code returned up on the SQLite timeline at http://www.sqlite.org/cgi/src/info/a586a4deeb253300 On Thu, Nov 7, 2013 at 11:21 AM, Simon Slavin wrote: > > On 7 Nov 2013, at 12:09am, SongbookDB > wrote: > > > The database seems to write fine. I have it loading into a datagrid in my > > Flash AIR desktop app, from where I read it and make edits to it. > > I don't understand how any SQLite app can return the error you gave: > > >> Flash gives the error - no such function: 'sqlite_version' > > All APIs that allow you to execute SQLite commands should let you execute > > SELECT sqlite_version() > > so perhaps you have better ask the Flash Pro people about this problem. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Shaun Thomson Owner - SongbookDB ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Disparity between query time in SQLite Administrator and Flash Pro
Gidday Stephen The database seems to write fine. I have it loading into a datagrid in my Flash AIR desktop app, from where I read it and make edits to it. On Thu, Nov 7, 2013 at 2:54 AM, Stephen Chrzanowski wrote: > The 15 seconds "smells" like a time out. I've found in the past where if > the database being accessed is in read only mode (Either by filesystem > permissions, or just the plain old READ ONLY file attribute) SQLite > pauses. I can dupe this with using any utility, be it the SQLite CLI or > PHP or SQLite Expert, etc. Changing the DB into something that is > read-only locks it down. Try doing something to the database that needs a > write and see if you get an error. > > > On Mon, Nov 4, 2013 at 6:12 AM, SongbookDB >wrote: > > > Thanks Clemens > > > > Flash gives the error - no such function: 'sqlite_version' > > > > SQLite Administrator says 3.5.1 > > > > I'll see what the Adobe community say. > > > > > > > > > > On Mon, Nov 4, 2013 at 9:30 PM, Clemens Ladisch > > wrote: > > > > > SongbookDB wrote: > > > > The database has an index that is being used according to EXPLAIN > QUERY > > > > PLAN in SQLite Administrator. Does Flash use the same SQL code? > > > > > > Probably not. Try "SELECT sqlite_version();". > > > > > > > > > Regards, > > > Clemens > > > ___ > > > sqlite-users mailing list > > > sqlite-users@sqlite.org > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > > -- > > Shaun Thomson > > Owner - SongbookDB > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Shaun Thomson Owner - SongbookDB ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Disparity between query time in SQLite Administrator and Flash Pro
Thanks Clemens Flash gives the error - no such function: 'sqlite_version' SQLite Administrator says 3.5.1 I'll see what the Adobe community say. On Mon, Nov 4, 2013 at 9:30 PM, Clemens Ladisch wrote: > SongbookDB wrote: > > The database has an index that is being used according to EXPLAIN QUERY > > PLAN in SQLite Administrator. Does Flash use the same SQL code? > > Probably not. Try "SELECT sqlite_version();". > > > Regards, > Clemens > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Shaun Thomson Owner - SongbookDB ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Second ORDER BY statement
Ah - thanks Clemens - that worked REALLY well! On Sun, Nov 3, 2013 at 10:22 PM, Clemens Ladisch wrote: > SongbookDB wrote: > > I'd now like to order the Language = "" rows by another column, "Artist", > > but cannot crack how to restructure the query to accommodate this. > > > > SELECT * FROM > > (SELECT * > > FROM table1 > > WHERE Language !="" COLLATE NOCASE > > ORDER BY Language COLLATE NOCASE) > > UNION ALL > > SELECT * > > FROM table1 > > WHERE Language ="" COLLATE NOCASE > > LIMIT 100 > > You sort them the same way you sort the other records: > > SELECT * FROM > (SELECT * > FROM table1 > WHERE Language != "" COLLATE NOCASE > ORDER BY Language COLLATE NOCASE) > UNION ALL > SELECT * FROM > (SELECT * > FROM table1 > WHERE Language = "" COLLATE NOCASE > ORDER BY Artist COLLATE NOCASE) > LIMIT 100 > > > Regards, > Clemens > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Shaun Thomson Owner - SongbookDB ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Disparity between query time in SQLite Administrator and Flash Pro
Hi Just wondering if anyone has heard of Flash adding time to queries? I have a query that is taking almost 5 seconds on a database in Flash, yet when I put the same query into SQLite Administrator with that database loaded, it takes 15ms. I set up a flash timer, and start it just before... sqlStatement.execute(-1, new Responder( pass, fail )); ... and stop it and trace it as soon as Flash hits pass(); - so there's nothing else going on apart from the query to eat up time. The database has an index that is being used according to EXPLAIN QUERY PLAN in SQLite Administrator. Does Flash use the same SQL code? It's as if it's ignoring the index. Here's the query (I know there are a lot of COLLATE NOCASE statements - it's because my current table columns are not COLLATE NOCASE, and I'm not yet ready to set up a new table that has COLLATE NOCASE columns to copy the data into): SELECT * FROM (SELECT * FROM table1 WHERE col1 !="" COLLATE NOCASE ORDER BY col1 COLLATE NOCASE , col2 COLLATE NOCASE ASC, col3 COLLATE NOCASE ASC, col4 COLLATE NOCASE ASC ) UNION ALL SELECT * FROM (SELECT * FROM table1 WHERE col1="" COLLATE NOCASE ORDER BY col2 COLLATE NOCASE ASC, col3COLLATE NOCASE ASC, col4 COLLATE NOCASE ASC) LIMIT 10 Thanks for your thoughts. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Second ORDER BY statement
Thanks Simon I'm yet to get to setting up a new table with COLLATE NOCASE columns and copy the old table's data into it. On Sun, Nov 3, 2013 at 11:01 PM, Simon Slavin wrote: > > On 3 Nov 2013, at 3:24am, SongbookDB wrote: > > > WHERE Language !="" COLLATE NOCASE > > ORDER BY Language COLLATE NOCASE) > > By the way, if every time you refer to your Language column you want it > colated NOCASE, it's far more efficient to do it when you define the column > in the table. > > CREATE TABLE ... (..., Language TEXT COLLATE NOCASE, ...) > > Then all collations of that column are handled NOCASE and you don't have > to keep specifying it in your code or even indexes. Note that this does > not change the actual values which are stored in the table: you still get > back out the exact form of text you put in. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Shaun Thomson Owner - SongbookDB ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Second ORDER BY statement
Not sure how if this is the way to reply, but thank you Igor. On Sun, Nov 3, 2013 at 3:16 PM, Igor Tandetnik wrote: > On 11/2/2013 11:24 PM, SongbookDB wrote: > >> I'm using the following query to first, display in asc order any records >> that have something in the language column, then any fields where the >> language column is an empty string (fyi I don't have nulls in this >> column). >> It works perfectly. >> >> I'd now like to order the Language = "" rows by another column, "Artist", >> but cannot crack how to restructure the query to accommodate this. >> >> SELECT * FROM >> (SELECT * >> FROM table1 >> WHERE Language !="" COLLATE NOCASE >> ORDER BY Language COLLATE NOCASE) >> UNION ALL >> SELECT * >> FROM table1 >> WHERE Language ="" COLLATE NOCASE >> LIMIT 100 >> > > select * from table1 > order by Language = '', Language collate nocase, Artist collate nocase; > > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Shaun Thomson Owner - SongbookDB ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Second ORDER BY statement
Hi I'm using the following query to first, display in asc order any records that have something in the language column, then any fields where the language column is an empty string (fyi I don't have nulls in this column). It works perfectly. I'd now like to order the Language = "" rows by another column, "Artist", but cannot crack how to restructure the query to accommodate this. SELECT * FROM (SELECT * FROM table1 WHERE Language !="" COLLATE NOCASE ORDER BY Language COLLATE NOCASE) UNION ALL SELECT * FROM table1 WHERE Language ="" COLLATE NOCASE LIMIT 100 Is it possible, and if so, would you mind showing me how? Thanks for your time and help. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
