Re: [sqlite] Reducing the output of a query

Copy-Paste dyslexia - The AS - Aliasing shouldn't be present in the GROUP-BY clause, obviously - thanks. [...] GROUP BY ItemA, ISNULL(ItemB,0), ISNULL(ItemC,0) On 2017/05/02 10:06 PM, R Smith wrote: Use a substitute for the NULL values. You will have to pick a suitable substitute that

Re: [sqlite] Reducing the output of a query

Use a substitute for the NULL values. You will have to pick a suitable substitute that doesn't confuse the other data. In this example I just use 0 as the substitute value, you might prefer another based on the true nature of your query (strings are also allowed). SELECT ItemA,

Re: [sqlite] Reducing the output of a query

Assuming each column is either NULL or a distinct item for each group, just use MAX() of each column adding the necessary group bys. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Joseph L. Casale Sent: Tuesday, May 2, 2017 3:55

[sqlite] Reducing the output of a query

I have a query produced from several left joins which follows the format: XXX ItemA NULL NULL XXX ItemA ItemB NULL XXX ItemA NULL ItemC I need to group the data by all columns, column 0 is trivial, however columns 1:3 can collapse when any non null field matches. In the above case this could

Re: [sqlite] Duplicate and Missing results using an IN operator in the WHERE clause

On 5/1/17, Gouranga Gupta wrote: > Hi. > > > According to Ticket UUID: 61fe97454c00bc4a5f5d826af02161d7df0a40c0 > Title "Duplicate result using an IN operator in the WHERE clause" > created on 2017-04-24 14:57:43 "Status" is "Closed and "Resolution" is > "Fixed". >

[sqlite] Duplicate and Missing results using an IN operator in the WHERE clause

Hi. According to Ticket UUID: 61fe97454c00bc4a5f5d826af02161d7df0a40c0 Title "Duplicate result using an IN operator in the WHERE clause" created on 2017-04-24 14:57:43 "Status" is "Closed and "Resolution" is "Fixed". However, using the latest version: sqlite-amalgamation-318.zip (and

Re: [sqlite] Thread notification for new record in a table.

> Le 2 mai 2017 à 09:48, John Found a écrit : > >> Reading your question I assume a single, multi-threaded, application. You >> could write a SQL function (see sqlite3_create_function_v2 and associates) >> which signal an event. And add a SQL trigger calling this

Re: [sqlite] Thread notification for new record in a table.

On Tue, 2 May 2017 09:38:30 +0200 Olivier Mascia wrote: > > Le 2 mai 2017 à 09:00, John Found a écrit : > > > > What is the best way (less CPU consuming) to put a thread in sleep and wake > > it up when new record has been written to a given table of

Re: [sqlite] Thread notification for new record in a table.

> Le 2 mai 2017 à 09:00, John Found a écrit : > > What is the best way (less CPU consuming) to put a thread in sleep and wake > it up when new record has been written to a given table of SQLite database? > > Now I am implementing this by polling and time based sleep, but

Re: [sqlite] Thread notification for new record in a table.

Rowan Worth wrote: > Is the database being updated by a separate process, or by another thread > in the same process? > [...] > If the latter then there's plenty of ways the thread updating the database > can notify the waiting thread, depending on your platform(s) and > language(s) of choice. If

Re: [sqlite] Thread notification for new record in a table.

Is the database being updated by a separate process, or by another thread in the same process? If the former, I don't think you've any choice but to poll the database. But by taking advantage of PRAGMA data_version and a clever schema you can make that polling pretty lightweight. If you really

[sqlite] Thread notification for new record in a table.

What is the best way (less CPU consuming) to put a thread in sleep and wake it up when new record has been written to a given table of SQLite database? Now I am implementing this by polling and time based sleep, but such solution is very dirty compromise, trading response time for CPU load. I