Re: [sqlite] Most Frequent Occurrence Problem

I am not sure exactly how I am to work in the coalesce function to get the result, I tried some experiments and I could get the first and or second fields in another column, but not as part of the bracketed group. This is the argument as it stands now, (without the coalesce function) how should

Re: [sqlite] Testing PRAGMA integrity_check functionality

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/12/11 06:44, George Eckert wrote: > Does anyone know of a "good" way to forfce an error in a database so > that PRAGMA integrity_check would produce an error? Truncating the database a bit after a vacuum will definitely do it, and it will

Re: [sqlite] Re versing Text not Numbers

Thank you for that, I hadn't been able to find a function, I will have to explore the link you posted. Cheers, Tim. Igor Tandetnik wrote: > > Macgyver7 wrote: >> I have a column of text that has numbers in some fields and words in >> others. >> I need to reverse

Re: [sqlite] Re versing Text not Numbers

Macgyver7 wrote: > I have a column of text that has numbers in some fields and words in others. > I need to reverse the letter order of the text eg. 'abcde' becomes 'edcba' > without changing the order of the fields with numbers. How do I reverse the > letter order

Re: [sqlite] Most Frequent Occurrence Problem

Macgyver7 wrote: > Thanks Igor, that works, however there is a problem. This will only return a > result when there are no null fields in the last two columns. The first > column has results in every field, but columns 2 and 3 don't have results in > every field. How

[sqlite] Re versing Text not Numbers

I have a column of text that has numbers in some fields and words in others. I need to reverse the letter order of the text eg. 'abcde' becomes 'edcba' without changing the order of the fields with numbers. How do I reverse the letter order and how do I avoid doing that to the numbers as

Re: [sqlite] Most Frequent Occurrence Problem

Thanks Igor, that works, however there is a problem. This will only return a result when there are no null fields in the last two columns. The first column has results in every field, but columns 2 and 3 don't have results in every field. How do I get around this not returning anything when

Re: [sqlite] Most Frequent Occurrence Problem

Macgyver7 wrote: > Many thanks Igor, that worked beautifully. If I wanted to put the three top > meanings in one column separated by commas, instead of three columns, is > there an easy way to do that? It would be good to have them in brackets too. > eg. (meaning 1,

Re: [sqlite] Most Frequent Occurrence Problem

one possibility: use || for concatenation. e.g. "(" || select(..) ||"," select() ||")" maybe there is a cleaner way in sqlite. -sean -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Macgyver7 Sent: Friday, December 02, 2011

Re: [sqlite] Most Frequent Occurrence Problem

Many thanks Igor, that worked beautifully. If I wanted to put the three top meanings in one column separated by commas, instead of three columns, is there an easy way to do that? It would be good to have them in brackets too. eg. (meaning 1, meaning 2, meaning 3) Thanks for the help, Tim.

Re: [sqlite] union-having bug

Gillman, David wrote: > Is this behavior known? The third query returns no rows even though bar = 1. > > sqlite> select ind, sum(foo) foo, sum(bar) bar from (select 1 ind, 0 foo, 1 > bar union select 1 ind, 1 foo, 0 bar) group by ind > having foo > 0; > ind|foo|bar >

Re: [sqlite] union-having bug

Hmm, the mailer changed my output. Here's what I meant: sqlite> select ind, sum(foo) foo, sum(bar) bar from (select 1 ind, 0 foo, 1 bar union select 1 ind, 1 foo, 0 bar) group by ind; ind|foo|bar 1|1|1 sqlite> select ind, sum(foo) foo, sum(bar) bar from (select 1 ind, 0 foo, 1 bar union

[sqlite] union-having bug

Hi, Is this behavior known? The third query returns no rows even though bar = 1. sqlite> select region, sum(edge) edge, sum(infra) infra from (select 1 region, 0 edge, 1 infra union select 1 region, 1 edge, 0 infra) group by region;region|edge|infra 1|1|1 sqlite> select ind, sum(foo) foo,

Re: [sqlite] Slow View when it is used in a where clause

> Specifically, when I said "using directly the select of the view", I mean > execute this query: > > EXPLAIN QUERY PLAN SELECT wg.*, count(DISTINCT w.idWord) AS itemsCount, > count(DISTINCT dw.Document_idDocument) AS documentsCount > > FROM WordGroup wg >    LEFT OUTER JOIN Word w ON

Re: [sqlite] Slow View when it is used in a where clause

Hello Pavel, Thank you for your answer. Probably, as you have commented, the problem is that the optimizer of SQLite does not work fine in this case. Specifically, when I said "using directly the select of the view", I mean execute this query: EXPLAIN QUERY PLAN SELECT wg.*,

Re: [sqlite] SQLite's FAIL ALGORITHM IN ONCONFLICT CLAUSE

On Fri, Dec 02, 2011 at 12:44:53AM -0800, destiny12 scratched on the wall: > > I m facing some problem in execution of sqlite's fail algorithm. Its > definition says that it don't rollback changes made prior to encountering > constraint voilation and changes to that row where constraint voilation

Re: [sqlite] Testing PRAGMA integrity_check functionality

On Fri, Dec 2, 2011 at 9:44 AM, George Eckert wrote: > Does anyone know of a "good" way to forfce an error in a database so that > PRAGMA integrity_check would produce an error? > We have lots of test procedures for SQLite in which we change individual bytes or individual

Re: [sqlite] Slow View when it is used in a where clause

> I have been analyzing the Query PLAN. In the query "Select * FROM > WordGroupView Where idWordGroup = 1;" the database engine perform a scan > over WordGroup table, and it is not necessary since the where clause is > defined. However, using directly the query of the view change the SCAN by a >

[sqlite] Testing PRAGMA integrity_check functionality

Does anyone know of a "good" way to forfce an error in a database so that PRAGMA integrity_check would produce an error? Thanks, George Eckert ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] SQLite's FAIL ALGORITHM IN ONCONFLICT CLAUSE

destiny12 wrote: > I m facing some problem in execution of sqlite's fail algorithm. Its > definition says that it don't rollback changes made prior to encountering > constraint voilation and changes to that row where constraint voilation is > occured and beyond never

Re: [sqlite] Most Frequent Occurrence Problem

Macgyver7 wrote: > I have a table with three columns, Col1, Col2 and Meaning. I have multiple > occurrences of distinct words within the column, Col2, with it's specific > meaning for each word listed in the Meaning column. There are multiple > occurrences of each

Re: [sqlite] Checkpoint time

On Fri, Dec 2, 2011 at 2:15 AM, Sreekumar TP wrote: > There is another reader but sharing the same connection. So checkpoint > process cannot be blocked by another reader. > The other reader doesn't have to be active - it merely needs to be holding open a read

Re: [sqlite] SQLite ordering data from multiple columns

On 2 Dec 2011, at 9:03am, Kit wrote: > SELECT id, name FROM emp WHERE name LIKE '%emp%' > UNION ALL > SELECT id, descr FROM emp WHERE descr LIKE '%emp%'; I note very interesting names for the columns in this one. The question I was asking was whether the second column was called 'name' for

Re: [sqlite] SQLite ordering data from multiple columns

2011/12/2, colombus : > I want to search this database I will search Emp Name & Emp Desc for example > If Search EMP I will get results as EMP1 , this is EMP1, EMP2, this is EMP2. > I need to order this search in such a way that I get the Emp Name Column > first then I will get

[sqlite] Most Frequent Occurrence Problem

I have a table with three columns, Col1, Col2 and Meaning. I have multiple occurrences of distinct words within the column, Col2, with it's specific meaning for each word listed in the Meaning column. There are multiple occurrences of each distinct word and meaning combination. How do I return

[sqlite] SQLite's FAIL ALGORITHM IN ONCONFLICT CLAUSE

I m facing some problem in execution of sqlite's fail algorithm. Its definition says that it don't rollback changes made prior to encountering constraint voilation and changes to that row where constraint voilation is occured and beyond never occur. I executed it. But when I update values after