Re: [sqlite] sqlite max arguments assistance

"Jungle Boogie" wrote... Dear Igor, From: Igor Tandetnik <i...@tandetnik.org> 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 sto

Re: [sqlite] sqlite max arguments assistance

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 <i...@tandetnik.org> >Sent: Mon, 22 Sep 2014 16:3

Re: [sqlite] sqlite max arguments assistance

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 >max(transaction_amount

Re: [sqlite] sqlite max arguments assistance

Dear Simon, From: Simon Slavin <slav...@bigfraud.org> Sent: Tue, 23 Sep 2014 00:29:32 +0100 To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] sqlite max arguments assistance > > >> On

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

Re: [sqlite] sqlite max arguments assistance

Dear Kees, From: Kees Nuyt <k.n...@zonnet.nl> 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_amou

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

Re: [sqlite] sqlite max arguments assistance

Dear Igor, From: Igor Tandetnik <i...@tandetnik.org> 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 -

Re: [sqlite] sqlite max arguments assistance

Dear Simon, From: Simon Slavin <slav...@bigfraud.org> Sent: Mon, 22 Sep 2014 22:22:00 +0100 To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] sqlite max arguments assistance > > > On 22

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

Re: [sqlite] sqlite max arguments assistance

Dear Igor, From: Igor Tandetnik <i...@tandetnik.org> 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 -

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

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

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

Re: [sqlite] sqlite max arguments assistance

Dear Igor, From: Igor Tandetnik <i...@tandetnik.org> 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 *

Re: [sqlite] sqlite max arguments assistance

Dear Simon, From: Simon Slavin <slav...@bigfraud.org> Sent: Mon, 22 Sep 2014 20:14:08 +0100 To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] sqlite max arguments assistance > > > On 2

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

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

[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