[sqlite] 2 different SQLite versions inside the same process space
On 10 May 2016, at 10:29pm, Steve Schow wrote: > are you saying that on UNIX, if two different versions of the sqlite3 binary > attempt to access a DB file at the same time?then the globals that are used > in the sqlite3 binaries related to locking may be different in the two > different binaries, and may result in DB corruption? I'd just like to note a difference between 'two different versions' which you wrote, and 'two different instances' as Richard wrote. The problem is not because a protocol has changed between versions of SQLite. Simon.
[sqlite] Good way for CEIL, or is there a better way
On 2016/05/10 11:05 PM, Cecil Westerhof wrote: > 2016-05-10 22:06 GMT+02:00 Darren Duncan : > >> On 2016-05-10 12:03 AM, Cecil Westerhof wrote: >> >>> But it looks like that the following is also acceptable: >>> ? >>> CAST(ROUND(100.0 * rank / outOf + 0.499) AS int) AS percentage >>> and it is a lot simpler. So probably I will go for this. >>> >> That might seem simpler but on edge cases it would fail. Having an >> if-then version is more reliable in general. Also less ambiguous as ROUND >> doesn't behave the same everywhere. -- Darren Duncan > > ?OK, I am back to my original version. ;-) > > But I made a view: > CREATE VIEW linkedinRankingPercentage AS > WITH percentage AS ( > SELECT date > , rank > , outOf > , 100.0 * rank / outOf AS percentage > , CAST(100.0 * rank / outOf AS int) AS castedPercentage > FROM linkedinRanking > ) > SELECT date > , rank > , outOf > , (CASE WHEN percentage = castedPercentage > THEN castedPercentage > ELSE castedPercentage + 1 > END) AS percentage > FROM percentage > ; > > It is probably not possible, but just to be sure. ;-) > I like to have linkedinRankingPercentage the same fields as > linkedinRanking, but with the calculated field percentage added. I now name > those fields twice in the creating of the view. Can this be done better? In > the first SELECT I could change the three fields to a *, but I like the > symmetry more. > > Would the view be a lot more expensive as the table, or if I do not need > the percentage, is it better to use the table instead of the view? > Sorry I'm late to this thread, but why not use a proper CEIL function inline? Any of the following two will work in all circumstances and are simple enough to be done without the view: (COALESCE(NULLIF(CAST(v AS int),v)+1,CAST(v AS int)) (CAST(v AS int) + CASE CAST(v AS int) WHEN v THEN 0 ELSE 1 END) Translated to your Query: SELECT date , rank , outOf , (COALESCE(NULLIF(CAST((100*rank/outof) AS int),(100*rank/outof))+1,CAST((100*rank/outof) AS int)) AS percentage FROM linkedinRanking OR SELECT date , rank , outOf , (CAST((100*rank/outof) AS int) + CASE CAST((100*rank/outof) AS int) WHEN (100*rank/outof) THEN 0 ELSE 1 END) AS percentage FROM linkedinRanking If you'd like to do a view and avoid repeating the calculation, I'd suggest a common table expression rather, like this perhaps: WITH PC(date, rank, outOf, prcnt) AS ( SELECT date, rank, outOf, (100*rank/outof) FROM linkedinRanking)SELECT date, rank, outOf, (COALESCE(NULLIF(CAST(prcnt AS int),prcnt)+1,CAST(prcnt AS int)) AS percentage FROM PC Although - I doubt this makes a significant improvement in the efficiency, if any. Better yet for this kind of thing would be: SELECT date, rank, outOf, (COALESCE(NULLIF(CAST(prcnt AS int),prcnt)+1,CAST(prcnt AS int)) AS percentage FROM (SELECT date, rank, outOf, (100*rank/outof) AS prcnt FROM linkedinRanking) HTH, Ryan
[sqlite] Good way for CEIL, or is there a better way
2016-05-10 22:06 GMT+02:00 Darren Duncan : > On 2016-05-10 12:03 AM, Cecil Westerhof wrote: > >> But it looks like that the following is also acceptable: >> ? >> CAST(ROUND(100.0 * rank / outOf + 0.499) AS int) AS percentage >> and it is a lot simpler. So probably I will go for this. >> > > That might seem simpler but on edge cases it would fail. Having an > if-then version is more reliable in general. Also less ambiguous as ROUND > doesn't behave the same everywhere. -- Darren Duncan ?OK, I am back to my original version. ;-) But I made a view: CREATE VIEW linkedinRankingPercentage AS WITH percentage AS ( SELECT date , rank , outOf , 100.0 * rank / outOf AS percentage , CAST(100.0 * rank / outOf AS int) AS castedPercentage FROM linkedinRanking ) SELECT date , rank , outOf , (CASE WHEN percentage = castedPercentage THEN castedPercentage ELSE castedPercentage + 1 END) AS percentage FROM percentage ; It is probably not possible, but just to be sure. ;-) I like to have linkedinRankingPercentage the same fields as linkedinRanking, but with the calculated field percentage added. I now name those fields twice in the creating of the view. Can this be done better? In the first SELECT I could change the three fields to a *, but I like the symmetry more. Would the view be a lot more expensive as the table, or if I do not need the percentage, is it better to use the table instead of the view? -- Cecil Westerhof
[sqlite] 2 different SQLite versions inside the same process space
On 10 May 2016 at 22:29, Steve Schow wrote: > I would like to understand this issue a little bit better? > On May 10, 2016, at 2:31 PM, Richard Hipp wrote: >> >> In unix, SQLite has to use global variables to work around the >> well-known design bugs in posix advisory locks. And so if you have >> two different instances of SQLite running on unix, they will use >> different global variables, causing them to break each others locks >> and you will get database corruption. > are you saying that on UNIX, if two different versions of the sqlite3 binary > attempt to access a DB file at the same time?then the globals that are used > in the sqlite3 binaries related to locking may be different in the two > different binaries, and may result in DB corruption? I read it as two different *copies*. It doesn't sound to me as if the versions have anything to do with it. -- Cheers -- Tim
[sqlite] 2 different SQLite versions inside the same process space
On 10/05/16 10:42, Andrey Gorbachev wrote: > I am a bit worried that the initialisation of 2 different versions of SQLite > would interfere with one another. Any advice? There is a way to do it, and I use it for my Python APSW extension as the recommended build instructions. It is especially helpful on MacOS as system components like Core Data use SQLite, and the loaders tend to force the system SQLite library to always be loaded. As a bonus, the approach below also results in faster code. What you need to do is create a .c file that near the top has these lines: #define SQLITE_API static #define SQLITE_EXTERN static #include "sqlite3.c" That causes all the SQLite non-private symbols to only have the scope of that .c file, and not leak outside of it. In the rest of the .c file put your C code that uses the SQLite API, and it will use the static included version (only). You are done. The reason this also gives faster code is that the compiler can inline the heck out of SQLite code since it knows it won't be used outside of the compilation unit. It does sometimes make debugging interesting though. Roger -- next part -- A non-text attachment was scrubbed... Name: signature.asc Type: application/pgp-signature Size: 181 bytes Desc: OpenPGP digital signature URL: <http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/attachments/20160510/8d7552f7/attachment.pgp>
[sqlite] 64bit DLL vs 32bit
sorry that is LISTDLLS no ' or space ... and not singular. On Tue, May 10, 2016 at 7:02 PM, J Decker wrote: > In general... > > while sqlite tool in question is running one could run listdll's in an > admin console window and see... listdll takes a executable name to > filter its list... > > maybe you have another compatible one in the path it's finding? > Because it's not Any CPU.
[sqlite] 64bit DLL vs 32bit
In general... while sqlite tool in question is running one could run listdll's in an admin console window and see... listdll takes a executable name to filter its list... maybe you have another compatible one in the path it's finding? Because it's not Any CPU.
[sqlite] 2 different SQLite versions inside the same process space
Hi, Microsoft Excel allows custom "Addins", which are all run in the same process space. We use Bloomberg Excel Addin, which uses SQLite 3.7.x and we have no control over what version of SQLite they use. We have our own Addin which uses SQLite 3.11.x and we do not want to use the same version as Bloomberg. Both Addins have their own separate databases and do not share any database connections or anything like that. I am a bit worried that the initialisation of 2 different versions of SQLite would interfere with one another. Any advice? Many thanks, Gorby
[sqlite] 2 different SQLite versions inside the same process space
On Tue, 10 May 2016 22:47 +0100, Tim Streater wrote: > > I read it as two different *copies*. It doesn't sound to me as if the > versions have anything to do with it. > Correct. Two different *copies*of the library. They can both have the same version number - that doesn't matter. -- D. Richard Hipp drh at sqlite.org
[sqlite] 64bit DLL vs 32bit
On 10 May 2016, at 4:56pm, Jeremy Nicoll wrote: > That suggests to me that sqldiff & sqlite3 only use a small fraction of > the code present in > a DLL, and the link only includes those functions in the resulting .exe. Correct. The SQLite tools do not use a DLL. They have the SQLite library included in their own code, supplied as one .h one .c file. Since the compiler has access to the raw source code it knows which functions are called and doesn't have to include the others. This is true of almost all programs which use SQLite on all platforms. SQLite is provided as two C files (the amalgamation .c and .h files) and you are expected to include them in your project. That way you don't have to find the ideal DLL to match your requirements. If your compilation process targets 32 bit or 16 bit or a mobile platform or a different OS, when your own code is being compiled for it, so is the SQLite code included in your project. It's only a certain kind of Windows user who wants DLLs for everything. If that's what you need you are going to have to make sure you get the right DLL. But the fact that most SQLite programmers don't use a DLL is why you're having trouble getting simple clear answers on this thread -- your problem is more about DLLs than it is about SQLite. Simon.
[sqlite] 64bit DLL vs 32bit
On Tue, 10 May 2016, at 16:26, Scott Robison wrote: > I believe the tools provided by the site statically like SQLite so no DLL > is required. The DLL is provided as a courtesy to those who do not want > to link their own apps statically. > > Not near a computer to confirm this, but I know for a fact that 32 / 64 > bit DLLs are not interchangeable. See > https://msdn.microsoft.com/en-us/library/windows/desktop/aa384231(v=vs.85).aspx Thanks for the link. I looked at the sizes of the DLLs and the tools .executables: 32-bit DLL 807 KB 64-bit DLL1644 KB sqldiff.exe 422 KB sqlite3.exe 661 KB sqlite3_analyser.exe 1912 KB That suggests to me that sqldiff & sqlite3 only use a small fraction of the code present in a DLL, and the link only includes those functions in the resulting .exe. Is that likely? Or are the .exe's compressed? -- Jeremy Nicoll - my opinions are my own.
[sqlite] 2 different SQLite versions inside the same process space
On 5/10/16, Andrey Gorbachev wrote: > Hi, > > Microsoft Excel allows custom "Addins", which are all run in the same > process space. We use Bloomberg Excel Addin, which uses SQLite 3.7.x and we > have no control over what version of SQLite they use. We have our own Addin > which uses SQLite 3.11.x and we do not want to use the same version as > Bloomberg. Both Addins have their own separate databases and do not share > any database connections or anything like that. > > I am a bit worried that the initialisation of 2 different versions of SQLite > would interfere with one another. Any advice? > In unix, SQLite has to use global variables to work around the well-known design bugs in posix advisory locks. And so if you have two different instances of SQLite running on unix, they will use different global variables, causing them to break each others locks and you will get database corruption. But as far as I know, that is not a problem on Windows. On the other hand, this is not something that we have ever tested. -- D. Richard Hipp drh at sqlite.org
[sqlite] 64bit DLL vs 32bit
On Tue, 10 May 2016, at 14:45, J Decker wrote: > On Tue, May 10, 2016 at 2:23 AM, Jeremy Nicoll > > I was under the impression that I'm using the 64-bit DLL on a W8.1 > > 64-bit system, with the 32-bit tools. Does that mean that there's > > some clever trick in the tools to make that work? > No it means you're using 64 bit tools or 32 bit dll. I downloaded the only pre-compiled Windows binaries for tools that were listed on the downloads page. They're described as 32-bit, but the DLL I'd downloaded is the 64-bit one. It seems to work... Quite a lot of non-SQLite apps are 32-bit ones, and they work too. So, are you certain that this must mean that there's a 32-bit SQLite DLL somewhere on my system that I didn't know about? And if that IS the case, why doesn't the SQLite website offer pre-built 64-bit tools for use with the 64-bit DLL? -- Jeremy Nicoll - my opinions are my own.
[sqlite] 2 different SQLite versions inside the same process space
I would like to understand this issue a little bit better? On May 10, 2016, at 2:31 PM, Richard Hipp wrote: > > In unix, SQLite has to use global variables to work around the > well-known design bugs in posix advisory locks. And so if you have > two different instances of SQLite running on unix, they will use > different global variables, causing them to break each others locks > and you will get database corruption. are you saying that on UNIX, if two different versions of the sqlite3 binary attempt to access a DB file at the same time?then the globals that are used in the sqlite3 binaries related to locking may be different in the two different binaries, and may result in DB corruption? If that is the case, then although the internal DB file format may be backwards compatible between versions of sqlite3, its very important that I take care not to allow two different versions of the SQLITE executable code attempt to access the DB file at the same time. As long as they are totally separate non-concurrent accesses, it sounds like it should be fine?but if they attempt concurrently, then concurrency locking between them can?t be garaunteed due to changes in the way you are handling it with globals as the code has evolved. On UNIX anyway. Do I have that right? That?s a very important thing to keep in mind with so many different versions of sqlite3 executable code floating around..its built into python a lot older then the sqlite3 binary I have installed, which might be different from what is compiled into fossil, etc..
[sqlite] SELECT DISTINCT question
Thanks. That's helpful. "Last visited" would explain that query.
But now a new puzzle:
> select col1, col2 from table group by col2
> is identical to select col1, distinct col2 from table
The SELECT railroad diagram does not allow DISTINCT in that position, only
right after SELECT?
Should that be:?
select distinct col1, col2 from table
Regards
David M Bennett FACS
Andl - A New Database Language - andl.org
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Keith Medcalf
> Sent: Tuesday, 10 May 2016 12:59 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] SELECT DISTINCT question
>
>
> select col1, aggregateFunction(col2) from table group by col3 order by
col1
>
> returns the result of the aggregate function applied to all "col2" values
in
> the "col3" group. The col1 value is the last visited row in the group
which
> triggered the aggregate, with a special case for MIN and MAX, where the
col1
> value is from the last visited row which contained the min or max value of
> col2.
>
> order by is, of course, applied to the result set after it is generated,
and
> may or may not affect the visitation order.
>
> select col1, col2 from table group by col2
>
> is identical to select col1, distinct col2 from table
>
> in that the table is sorted by col2 and each col2 value is reported only
> once. The col1 value is from the last visited row in each group. Order
by
> may be applied "after the fact" to order the result set. Order by may or
may
> not affect the visitation order.
>
> (Note that "last visited" is often stated as "some random row" because the
> visitation order is an implementation detail of the query planner and may
> change from query to query based on the "shape" of the data and the
> particulars of how the query is solved internally.)
>
>
> > -Original Message-
> > From: sqlite-users-bounces at mailinglists.sqlite.org
> > [mailto:sqlite-users- bounces at mailinglists.sqlite.org] On Behalf Of
> > dandl
> > Sent: Monday, 9 May, 2016 18:19
> > To: 'SQLite mailing list'
> > Subject: Re: [sqlite] SELECT DISTINCT question
> >
> > The interesting thing about this query is that you can drop any of
> > DISTINCT, GROUP BY or ORDER BY and get the same result.
> >
> > But my question was not "how can I rewrite my query?". It was: how
> > does Sqlite interpret this SQL, given that it's probably invalid?
> >
> > Andl generates code for both Sqlite and Postgres, and I need to know
> > what that code does.
> >
> > Regards
> > David M Bennett FACS
> >
> > Andl - A New Database Language - andl.org
> >
> >
> > > -Original Message-
> > > From: sqlite-users-bounces at mailinglists.sqlite.org
> > > [mailto:sqlite-users- bounces at mailinglists.sqlite.org] On Behalf Of
> > > Scott Robison
> > > Sent: Monday, 9 May 2016 4:13 PM
> > > To: SQLite mailing list
> > > Subject: Re: [sqlite] SELECT DISTINCT question
> > >
> > > On Sun, May 8, 2016 at 7:45 PM, dandl wrote:
> > >
> > > > Just to add to the below:
> > > >
> > > > S#| SNAME | STATUS | CITY
> > > > ---
> > > > S1| Smith | 20 | London
> > > > S2| Jones | 10 | Paris
> > > > S3| Blake | 30 | Paris
> > > > S4| Clark | 20 | London
> > > > S5| Adams | 30 | Athens
> > > >
> > > > SELECT DISTINCT "CITY" FROM "S" GROUP BY "CITY" ORDER BY "S#" ASC
> > > > ;
> > > >
> > > > CITY
> > > > --
> > > > Paris
> > > > London
> > > > Athens
> > > >
> > > > I don't find it easy to explain this result.
> > > >
> > >
> > > My guess based on the available data is that, since you don't
> > > specify
> > which
> > > "S#" you want associated with each city, it is picking the max of
> > > each (coincidentally). If you want the minimum S# value, this seems to
> work:
> > >
> > > select distinct city from s group by city order by min("S#") asc;
> > >
> > > I'm not sure if that *should* work per "standard" SQL, but it does
> > > with SQLite. I'd have expected something like this to be necessary:
> > >
> > > select city, min("S#") as x from s group by city order by x asc;
> > >
> > > And if you only want the city:
> > >
> > > select city from (select city, min("S#") as x from s group by city
> > > order
> > by x
> > > asc);
> > >
> > > But I'm not a SQL master.
> > >
> > > Distinct used with group by seems redundant, but again, I might just
> > > not understand how they are useful together.
> > >
> > > --
> > > Scott Robison
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users at mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
[sqlite] 2 different SQLite versions inside the same process space
IF you have two different versions of SQLite linked into the same executable, both accessing the same database, then the problem that the globals work around can happen. It won't happen if different processes use different versions of SQLite (say two versions of the sqlite3 binary, or sqlite3 versus Python, etc), nor is there an issue if a single process with multiple versions linked is used to access different database files (say if one version is part of nss used to access the system certificate database and the other version is what you compile in to access your databases). The gist of the problem is that if a single process has two file descriptors open on the same file, and one holds a lock, closing the other file descriptor clears the lock. SQLite's Unix VFS puts the close in a global queue to close later to prevent this. -scott On Tue, May 10, 2016 at 2:29 PM, Steve Schow wrote: > > I would like to understand this issue a little bit better? > > > On May 10, 2016, at 2:31 PM, Richard Hipp wrote: >> >> In unix, SQLite has to use global variables to work around the >> well-known design bugs in posix advisory locks. And so if you have >> two different instances of SQLite running on unix, they will use >> different global variables, causing them to break each others locks >> and you will get database corruption. > > > are you saying that on UNIX, if two different versions of the sqlite3 binary > attempt to access a DB file at the same time?then the globals that are used > in the sqlite3 binaries related to locking may be different in the two > different binaries, and may result in DB corruption? > > If that is the case, then although the internal DB file format may be > backwards compatible between versions of sqlite3, its very important that I > take care not to allow two different versions of the SQLITE executable code > attempt to access the DB file at the same time. As long as they are totally > separate non-concurrent accesses, it sounds like it should be fine?but if > they attempt concurrently, then concurrency locking between them can?t be > garaunteed due to changes in the way you are handling it with globals as the > code has evolved. On UNIX anyway. Do I have that right? > > That?s a very important thing to keep in mind with so many different versions > of sqlite3 executable code floating around..its built into python a lot older > then the sqlite3 binary I have installed, which might be different from what > is compiled into fossil, etc.. > > > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Count connections
Hi, How do we know how many connections a database has ? Is it possible to query it using SQL ? Is there a simple command from the sqlite3 shell client ? -- Marco Arthur @ (M)arco Creatives
[sqlite] Count connections
On 5/10/16, Marco Silva wrote: > Hi, > > How do we know how many connections a database has ? Is it possible > to query it using SQL ? Is there a simple command from the sqlite3 > shell client ? > A "connection" to a database is an open file descriptor. Some operating systems have the ability to show how many times a particular file is open, but others do not, and even among those that do, the mechanism is not standardized. Hence, there is no way of knowing in general how many connections there are on an SQLite database. -- D. Richard Hipp drh at sqlite.org
[sqlite] Good way for CEIL, or is there a better way
On 2016-05-10 12:03 AM, Cecil Westerhof wrote: > But it looks like that the following is also acceptable: > ? > CAST(ROUND(100.0 * rank / outOf + 0.499) AS int) AS percentage > and it is a lot simpler. So probably I will go for this. That might seem simpler but on edge cases it would fail. Having an if-then version is more reliable in general. Also less ambiguous as ROUND doesn't behave the same everywhere. -- Darren Duncan
[sqlite] Good way for CEIL, or is there a better way
On 10 May 2016 at 08:31, Darren Duncan wrote: > The Ceiling function is not that simple, unless you know that your rank > and outOf are always non-negative numbers. If they might be negative, you > would -1 rather than +1 when the result is negative. -- Darren Duncan > Yeah you can't always add one. But you never need to subtract one - ceil always rounds towards positive infinity which is equivalent to integer truncation over the range of negative reals. I can't see a better way to implement ceil/floor in sqlite than using an int cast to truncate. -Rowan
[sqlite] SELECT DISTINCT question
> bounces at mailinglists.sqlite.org] On Behalf Of Keith Medcalf > Why are you using BOTH distinct and group by on the same column? You only > need one or the other. If you are redundantly redundant I would hope that > the optimizer makes redundant (as in gets rid of, for those that are not > English) the redundancies ... This is generated code. Since Andl does not allow any duplicate rows, every SELECT gets a DISTINCT unless the query provably cannot generate duplicates. You need both GROUP BY and DISTINCT in cases where there is an aggregate function (and some others). Say: SELECT DISTINCT SUM(X) AS Y FROM T GROUP BY Z; There is no way to predict from the query how many rows this will generate. Without DISTINCT it can generate duplicates. My question was really about why Sqlite did not complain on what is actually not a valid query. [Andl is still a work in progress.] Regards David M Bennett FACS Andl - A New Database Language - andl.org
[sqlite] SELECT DISTINCT question
Can you point me to something in the docs? It actually isn't quite the same.
Yes, I have discovered the Sqlite behaviour with aggregate functions. In
Postgres and standard SQL it's an error -- the rule is that every column has to
be in the GROUP BY if not used in the SELECT list or as input to an aggregate
function. Sqlite just punts.
I didn't know about DISTINCT. The standard rule here is a bit different: every
column in the ORDER BY must be in the select list. Since Andl generates SQL it
helps if I know what that SQL will do.
Regards
David M Bennett FACS
Andl - A New Database Language - andl.org
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Hick Gunter
> Sent: Monday, 9 May 2016 4:29 PM
> To: 'SQLite mailing list'
> Subject: Re: [sqlite] SELECT DISTINCT question
>
> This is documented behaviour for SQLite:
>
> SELECT a, MAX(b) table;
>
> Will return (one of) the a value(s) that comes from the same row as the
> MAX(b).
>
> If there are not exactly on of MIN or MAX aggregate functions, SQLite is free
> to pick any row (within a group) to return non-aggregated columns from. Thus:
>
> Select a,SUM(b), c from table group by a;
>
> will return one of the c values from each group of a values.
>
> The same thing applies for DISTINCT.
>
>
> -Urspr?ngliche Nachricht-
> Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] Im Auftrag von Scott Robison
> Gesendet: Montag, 09. Mai 2016 08:13
> An: SQLite mailing list
> Betreff: Re: [sqlite] SELECT DISTINCT question
>
> On Sun, May 8, 2016 at 7:45 PM, dandl wrote:
>
> > Just to add to the below:
> >
> > S#| SNAME | STATUS | CITY
> > ---
> > S1| Smith | 20 | London
> > S2| Jones | 10 | Paris
> > S3| Blake | 30 | Paris
> > S4| Clark | 20 | London
> > S5| Adams | 30 | Athens
> >
> > SELECT DISTINCT "CITY" FROM "S" GROUP BY "CITY" ORDER BY "S#" ASC ;
> >
> > CITY
> > --
> > Paris
> > London
> > Athens
> >
> > I don't find it easy to explain this result.
> >
>
> My guess based on the available data is that, since you don't specify which
> "S#" you want associated with each city, it is picking the max of each
> (coincidentally). If you want the minimum S# value, this seems to work:
>
> select distinct city from s group by city order by min("S#") asc;
>
> I'm not sure if that *should* work per "standard" SQL, but it does with
> SQLite. I'd have expected something like this to be necessary:
>
> select city, min("S#") as x from s group by city order by x asc;
>
> And if you only want the city:
>
> select city from (select city, min("S#") as x from s group by city order by x
> asc);
>
> But I'm not a SQL master.
>
> Distinct used with group by seems redundant, but again, I might just not
> understand how they are useful together.
>
> --
> Scott Robison
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: hick at scigames.at
>
> This communication (including any attachments) is intended for the use of the
> intended recipient(s) only and may contain information that is confidential,
> privileged or legally protected. Any unauthorized use or dissemination of
> this communication is strictly prohibited. If you have received this
> communication in error, please immediately notify the sender by return e-mail
> message and delete all copies of the original communication. Thank you for
> your cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 64bit DLL vs 32bit
On Mon, 9 May 2016, at 15:48, jicman at barrioinvi.net wrote: > Well, I can not use the SQLite 64bit DLL in a 64bit environment with a > 32bit application. I was under the impression that I'm using the 64-bit DLL on a W8.1 64-bit system, with the 32-bit tools. Does that mean that there's soe clever trick in the tools to make that work? -- Jeremy Nicoll - my opinions are my own.
[sqlite] SELECT DISTINCT question
The interesting thing about this query is that you can drop any of DISTINCT,
GROUP BY or ORDER BY and get the same result.
But my question was not "how can I rewrite my query?". It was: how does
Sqlite interpret this SQL, given that it's probably invalid?
Andl generates code for both Sqlite and Postgres, and I need to know what
that code does.
Regards
David M Bennett FACS
Andl - A New Database Language - andl.org
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Scott Robison
> Sent: Monday, 9 May 2016 4:13 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] SELECT DISTINCT question
>
> On Sun, May 8, 2016 at 7:45 PM, dandl wrote:
>
> > Just to add to the below:
> >
> > S#| SNAME | STATUS | CITY
> > ---
> > S1| Smith | 20 | London
> > S2| Jones | 10 | Paris
> > S3| Blake | 30 | Paris
> > S4| Clark | 20 | London
> > S5| Adams | 30 | Athens
> >
> > SELECT DISTINCT "CITY" FROM "S" GROUP BY "CITY" ORDER BY "S#" ASC ;
> >
> > CITY
> > --
> > Paris
> > London
> > Athens
> >
> > I don't find it easy to explain this result.
> >
>
> My guess based on the available data is that, since you don't specify
which
> "S#" you want associated with each city, it is picking the max of each
> (coincidentally). If you want the minimum S# value, this seems to work:
>
> select distinct city from s group by city order by min("S#") asc;
>
> I'm not sure if that *should* work per "standard" SQL, but it does with
> SQLite. I'd have expected something like this to be necessary:
>
> select city, min("S#") as x from s group by city order by x asc;
>
> And if you only want the city:
>
> select city from (select city, min("S#") as x from s group by city order
by x
> asc);
>
> But I'm not a SQL master.
>
> Distinct used with group by seems redundant, but again, I might just not
> understand how they are useful together.
>
> --
> Scott Robison
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 64bit DLL vs 32bit
On Tue, May 10, 2016 at 9:56 AM, Jeremy Nicoll < jn.ml.sqlu.725 at letterboxes.org> wrote: > On Tue, 10 May 2016, at 16:26, Scott Robison wrote: > > > I believe the tools provided by the site statically like SQLite so no DLL > > is required. The DLL is provided as a courtesy to those who do not want > > to link their own apps statically. > > > > Not near a computer to confirm this, but I know for a fact that 32 / 64 > > bit DLLs are not interchangeable. See > > > https://msdn.microsoft.com/en-us/library/windows/desktop/aa384231(v=vs.85).aspx > > Thanks for the link. > > I looked at the sizes of the DLLs and the tools .executables: > > 32-bit DLL 807 KB > 64-bit DLL1644 KB > > sqldiff.exe 422 KB > sqlite3.exe 661 KB > sqlite3_analyser.exe 1912 KB > > That suggests to me that sqldiff & sqlite3 only use a small fraction of > the code present in > a DLL, and the link only includes those functions in the resulting .exe. > Is that likely? Or > are the .exe's compressed? > I'm fairly confident they don't use 100% of the SQLite code in each, and I don't believe they are compressed. However, statically linking SQLite directly, particularly in amalgamation form, potentially opens up a lot of optimization opportunities that aren't available in a DLL where you don't know which APIs might be needed by any given program. -- Scott Robison
[sqlite] 64bit DLL vs 32bit
On May 10, 2016 8:48 AM, "Jeremy Nicoll" wrote: > > On Tue, 10 May 2016, at 14:45, J Decker wrote: > > On Tue, May 10, 2016 at 2:23 AM, Jeremy Nicoll > > > > I was under the impression that I'm using the 64-bit DLL on a W8.1 > > > 64-bit system, with the 32-bit tools. Does that mean that there's > > > some clever trick in the tools to make that work? > > > No it means you're using 64 bit tools or 32 bit dll. > > I downloaded the only pre-compiled Windows binaries for tools that were > listed on the downloads page. They're described as 32-bit, but the DLL > I'd downloaded is the 64-bit one. > > It seems to work... > > Quite a lot of non-SQLite apps are 32-bit ones, and they work too. > > So, are you certain that this must mean that there's a 32-bit SQLite DLL > somewhere on my system that I didn't know about? > > And if that IS the case, why doesn't the SQLite website offer pre-built > 64-bit > tools for use with the 64-bit DLL? I believe the tools provided by the site statically like SQLite so no DLL is required. The DLL is provided as a courtesy to those who do not want to link their own apps statically. Not near a computer to confirm this, but I know for a fact that 32 / 64 bit DLLs are not interchangeable. See https://msdn.microsoft.com/en-us/library/windows/desktop/aa384231(v=vs.85).aspx
[sqlite] Good way for CEIL, or is there a better way
2016-05-10 2:31 GMT+02:00 Darren Duncan :
> On 2016-05-09 4:24 AM, Cecil Westerhof wrote:
>
>> I need to have a CEIL function in SQLite. This is the way I implemented
>> it:
>> WITH percentage AS (
>> SELECT date
>> , 100.0 * rank / outOf AS percentage
>> ,
>> ??
>> CAST(100.0 * rank / outOf AS int) AS castedPercentage
>> FROM ranking
>> )
>> SELECT date
>> , (CASE WHEN percentage = castedPercentage
>> THEN castedPercentage
>> ELSE castedPercentage + 1
>> END) AS percentage
>> FROM percentage
>>
>> Is this a good way, or is there a better way?
>>
>
> The Ceiling function is not that simple, unless you know that your rank
> and outOf are always non-negative numbers. If they might be negative, you
> would -1 rather than +1 when the result is negative.
?I did not give all information. Both rank and outOf are at least 1 and
rank is <= outOf. So that should not be a problem.
But it looks like that the following is also acceptable:
?
CAST(ROUND(100.0 * rank / outOf + 0.499) AS int) AS percentage
and it is a lot simpler. So probably I will go for this.
For the curious, this is how I defined the table:
CREATE TABLE linkedinRanking (
dateTEXTNOT NULL DEFAULT CURRENT_DATE,
rankINTEGER NOT NULL,
outOfINTEGER NOT NULL,
CONSTRAINT formatDate CHECK(date = date(strftime('%s', date),
'unixepoch')),
CONSTRAINT notInFuture CHECK(date <= date()),
CONSTRAINT rankIsInt CHECK(TYPEOF(rank) = 'integer'),
CONSTRAINT outOfIsInt CHECK(TYPEOF(outOf) = 'integer'),
CONSTRAINT rankGEOne CHECK(rank >= 1),
CONSTRAINT rankLEOutOf CHECK(rank <= outOf),
CONSTRAINT outOfGEOne CHECK(outOf >= 1),
PRIMARY KEY(date)
);
?Maybe I should rename the date field. ;-)?
--
Cecil Westerhof
[sqlite] 64bit DLL vs 32bit
On Tue, May 10, 2016 at 2:23 AM, Jeremy Nicoll wrote: > On Mon, 9 May 2016, at 15:48, jicman at barrioinvi.net wrote: > >> Well, I can not use the SQLite 64bit DLL in a 64bit environment with a >> 32bit application. > > I was under the impression that I'm using the 64-bit DLL on a W8.1 > 64-bit system, > with the 32-bit tools. Does that mean that there's soe clever trick in > the tools to make > that work? No it means you're using 64 bit tools or 32 bit dll. > > -- > Jeremy Nicoll - my opinions are my own. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
