Re: [sqlite] sqlite max arguments assistance

"Jungle Boogie" wrote... Dear Igor, From: Igor Tandetnik Sent: Mon, 22 Sep 2014 16:34:18 -0400 To: sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite max arguments assistance Just as I thought. You are storing your values as tex

Re: [sqlite] sqlite max arguments assistance

half Of Jungle Boogie >Sent: Monday, 22 September, 2014 15:03 >To: sqlite-users@sqlite.org >Subject: Re: [sqlite] sqlite max arguments assistance > >Dear Igor, > >From: Igor Tandetnik >Sent: Mon, 22 Sep 2014 16:34:18 -0400 >

Re: [sqlite] sqlite max arguments assistance

.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of Jungle Boogie >Sent: Monday, 22 September, 2014 13:12 >To: sqlite-users@sqlite.org >Subject: [sqlite] sqlite max arguments assistance > >Hello All, > >select * from august where transaction_amount = (select >ma

Re: [sqlite] sqlite max arguments assistance

Dear Simon, From: Simon Slavin Sent: Tue, 23 Sep 2014 00:29:32 +0100 To: General Discussion of SQLite Database Subject: Re: [sqlite] sqlite max arguments assistance > > >> On 23 Sep 2014, at 12:24am, Jungle Boogie wrote: >> >&g

Re: [sqlite] sqlite max arguments assistance

> On 23 Sep 2014, at 12:24am, Jungle Boogie wrote: > > I did this: > sqlite> create table august > (MERCHANT_ID,DBA,WHITELABEL_ID,ORDER_ID,TRANSACTION_DISPLAY_DATE,TYPE,STATE,TRANSACTION_AMOUNT); > sqlite> .separator "," > sqlite> .import portalUseMonthly_20140901.csv august > > doing: > sqlite

Re: [sqlite] sqlite max arguments assistance

Dear Kees, From: Kees Nuyt Sent: Mon, 22 Sep 2014 23:59:52 +0200 To: sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite max arguments assistance > > and/or the receiving table doesn't define column > transaction_amount as a nume

Re: [sqlite] sqlite max arguments assistance

On 22 Sep 2014, at 10:25pm, Jungle Boogie wrote: > Actually, none of the fields have the dollar sign, that's my mistake. Can I > tell sqlite pre or post import of the csv that the field is number or will it > always take it as it? See the section on CSV Import in

Re: [sqlite] sqlite max arguments assistance

On Mon, 22 Sep 2014 14:02:57 -0700, Jungle Boogie wrote: > Igor Tandetnik > wrote Mon, 22 Sep 2014 16:34:18 -0400 >> >> Just as I thought. You are storing your values as text - not as numbers - and >> comparing them accordingly, in alphabetical order. > > Sorry, I'm not certain I know the answe

Re: [sqlite] sqlite max arguments assistance

Dear Igor, From: Igor Tandetnik Sent: Mon, 22 Sep 2014 16:34:18 -0400 To: sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite max arguments assistance > > > Just as I thought. You are storing your values as text - not as numbers - and &g

Re: [sqlite] sqlite max arguments assistance

Dear Simon, From: Simon Slavin Sent: Mon, 22 Sep 2014 22:22:00 +0100 To: General Discussion of SQLite Database Subject: Re: [sqlite] sqlite max arguments assistance > > > On 22 Sep 2014, at 10:02pm, Jungle Boogie wrote: > >> I

Re: [sqlite] sqlite max arguments assistance

On 22 Sep 2014, at 10:02pm, Jungle Boogie wrote: > Is there anything I can do post export from the other database to change the > values correctly? You need to strip the dollar signs off at some stage. Ideally you can do it in the CSV file before you import that into SQLite. Alternatively you

Re: [sqlite] sqlite max arguments assistance

Dear Igor, From: Igor Tandetnik Sent: Mon, 22 Sep 2014 16:34:18 -0400 To: sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite max arguments assistance > > > Just as I thought. You are storing your values as text - not as numbers - and &g

Re: [sqlite] sqlite max arguments assistance

On 9/22/2014 4:08 PM, jungle Boogie wrote: Hi Igor, On 22 September 2014 12:52, Igor Tandetnik wrote: Dollar sign or not, the outcome you observe suggests that the values are stored as strings. What does this query return? select typeof(transaction_amount), count(*) from august group by 1;

Re: [sqlite] sqlite max arguments assistance

Hi Igor, On 22 September 2014 12:52, Igor Tandetnik wrote: > > > Dollar sign or not, the outcome you observe suggests that the values are > stored as strings. What does this query return? > > select typeof(transaction_amount), count(*) from august group by 1; > > My guess is that most, if not all,

Re: [sqlite] sqlite max arguments assistance

On 9/22/2014 3:42 PM, Jungle Boogie wrote: From: Igor Tandetnik The fact that the result is printed complete with $ sign suggests strongly that the values are stored, and compared, as strings. '$999.63' > '$16695.36' when using alphabetical comparison. This is my mistake--ignore the dollar si

Re: [sqlite] sqlite max arguments assistance

Dear Igor, From: Igor Tandetnik Sent: Mon, 22 Sep 2014 15:25:43 -0400 To: sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite max arguments assistance > > On 9/22/2014 3:12 PM, Jungle Boogie wrote: >> select * from august where transa

Re: [sqlite] sqlite max arguments assistance

Dear Simon, From: Simon Slavin Sent: Mon, 22 Sep 2014 20:14:08 +0100 To: General Discussion of SQLite Database Subject: Re: [sqlite] sqlite max arguments assistance > > > On 22 Sep 2014, at 8:12pm, Jungle Boogie wrote: > >>

Re: [sqlite] sqlite max arguments assistance

I feel sure the transaction amounts are strings, not numbers. Here is a quick example: create temp table gigo(a real) insert into gigo values ('$5.00') select a, typeof(a) from gigo gives: $5.00 text If you can remove the dollar signs in the CSV file you should do better. Hope this helps,

Re: [sqlite] sqlite max arguments assistance

On 9/22/2014 3:12 PM, Jungle Boogie wrote: select * from august where transaction_amount = (select max(transaction_amount) from august) This statement should show be the merchant account with the top most expensive transaction from my table called august. Result: $999.63 The fact that the res

Re: [sqlite] sqlite max arguments assistance

On 22 Sep 2014, at 8:12pm, Jungle Boogie wrote: > Result: > $999.63 > (I trimmed out other items that I can't show). > > Same results with this: select max(transaction_amount) from august > $999.63 > > > But this is NOT the most expensive amount, but it is for a three digit dollar > amount. >

[sqlite] sqlite max arguments assistance

Hello All, select * from august where transaction_amount = (select max(transaction_amount) from august) This statement should show be the merchant account with the top most expensive transaction from my table called august. Result: $999.63 (I trimmed out other items that I can't show). Same res