sqlite max arguments assistance

classic Classic list List threaded Threaded
21 messages Options
12
Reply | Threaded
Open this post in threaded view
|

sqlite max arguments assistance

Jungle Boogie
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 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.


For example, this record is much higher in terms of transaction_amount:
$16695.36

This is a csv file that I've imported and I'm using SQLiteSpy with sqlite
3.8.6 as well as
FreeBSD lyander-fbsd 10.0-RELEASE-p9 FreeBSD 10.0-RELEASE-p9 #0: Mon Sep 15
14:32:29 UTC 2014
[hidden email]:/usr/obj/usr/src/sys/GENERIC  i386
also with sqlite3.8.6


How am i misunderstanding max?
--
inum: 883510009027723
sip: [hidden email]
xmpp: [hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite max arguments assistance

Simon Slavin-3

On 22 Sep 2014, at 8:12pm, Jungle Boogie <[hidden email]> 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.
>
>
> For example, this record is much higher in terms of transaction_amount:
> $16695.36

What is the affinity of the transaction_amount column of your august table ?

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite max arguments assistance

Igor Tandetnik-2
In reply to this post by Jungle Boogie
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 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.
--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite max arguments assistance

Gerry Snyder-4
In reply to this post by Jungle Boogie
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,

Gerry

On 9/22/2014 12:12 PM, Jungle Boogie wrote:

> 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 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.
>
>
> For example, this record is much higher in terms of transaction_amount:
> $16695.36
>
> This is a csv file that I've imported and I'm using SQLiteSpy with sqlite
> 3.8.6 as well as
> FreeBSD lyander-fbsd 10.0-RELEASE-p9 FreeBSD 10.0-RELEASE-p9 #0: Mon Sep 15
> 14:32:29 UTC 2014
> [hidden email]:/usr/obj/usr/src/sys/GENERIC  i386
> also with sqlite3.8.6
>
>
> How am i misunderstanding max?

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite max arguments assistance

Jungle Boogie
In reply to this post by Simon Slavin-3
Dear Simon,
--------------------------------------------
From: Simon Slavin <[hidden email]>
Sent:  Mon, 22 Sep 2014 20:14:08 +0100
To: General Discussion of SQLite Database <[hidden email]>
Subject: Re: [sqlite] sqlite max arguments assistance

>
>
> On 22 Sep 2014, at 8:12pm, Jungle Boogie <[hidden email]> 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.
>>
>>
>> For example, this record is much higher in terms of transaction_amount:
>> $16695.36
>
> What is the affinity of the transaction_amount column of your august table ?

Sorry, I don't know what this means. It looks like 50 grand is the largest
transaction amount, although Excel is having a hard time sorting the data well.


>
> Simon.
--
inum: 883510009027723
sip: [hidden email]
xmpp: [hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite max arguments assistance

Jungle Boogie
In reply to this post by Igor Tandetnik-2
Dear Igor,
--------------------------------------------
From: Igor Tandetnik <[hidden email]>
Sent:  Mon, 22 Sep 2014 15:25:43 -0400
To: [hidden email]
Subject: 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 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 sign. I should have noted this
originally. Forgive me!

 
--
inum: 883510009027723
sip: [hidden email]
xmpp: [hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite max arguments assistance

Igor Tandetnik-2
On 9/22/2014 3:42 PM, Jungle Boogie wrote:
> From: Igor Tandetnik <[hidden email]>
>> 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 sign. I should have noted this
> originally. Forgive me!

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, rows would report
typeof(transaction_amount) as 'text'.
--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite max arguments assistance

Jungle Boogie
Hi Igor,
On 22 September 2014 12:52, Igor Tandetnik <[hidden email]> 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, rows would report
> typeof(transaction_amount) as 'text'.
>

typeof(transaction_amount)count(*)
text 135388


> --
> Igor Tandetnik
>


--
-------
inum: 883510009027723
sip: [hidden email]
xmpp: [hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite max arguments assistance

Igor Tandetnik-2
On 9/22/2014 4:08 PM, jungle Boogie wrote:

> Hi Igor,
> On 22 September 2014 12:52, Igor Tandetnik <[hidden email]> 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, rows would report
>> typeof(transaction_amount) as 'text'.
>>
>
> typeof(transaction_amount)count(*)
> text 135388

Just as I thought. You are storing your values as text - not as numbers
- and comparing them accordingly, in alphabetical order.
--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite max arguments assistance

Jungle Boogie
Dear Igor,
--------------------------------------------
From: Igor Tandetnik <[hidden email]>
Sent:  Mon, 22 Sep 2014 16:34:18 -0400
To: [hidden email]
Subject: Re: [sqlite] sqlite max arguments assistance
>
>
> 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 answer to this as I don't generate the data.
Opening the csv file in Excel, I did have to change the transaction_amount
column from "general" to "number". I think your assumptions are correct,
though. I'll see if I can look at the values in the other database.
 
I know the data is generated from a different database and a file is created,
portalusemonthly.csv that's sent to a location where I can get it?

Is there anything I can do post export from the other database to change the
values correctly?

--
inum: 883510009027723
sip: [hidden email]
xmpp: [hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite max arguments assistance

Simon Slavin-3

On 22 Sep 2014, at 10:02pm, Jungle Boogie <[hidden email]> 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 may be able to do the import into SQLite then issue the following command:

UPDATE august SET transaction_amount = REPLACE(transaction_amount,'$','')

before you do your SELECT.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite max arguments assistance

Jungle Boogie
Dear Simon,
--------------------------------------------
From: Simon Slavin <[hidden email]>
Sent:  Mon, 22 Sep 2014 22:22:00 +0100
To: General Discussion of SQLite Database <[hidden email]>
Subject: Re: [sqlite] sqlite max arguments assistance

>
>
> On 22 Sep 2014, at 10:02pm, Jungle Boogie <[hidden email]> 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 may be able to do the import into SQLite then issue the following command:
>
> UPDATE august SET transaction_amount = REPLACE(transaction_amount,'$','')
>
> before you do your SELECT.
>

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?

> Simon.
--
inum: 883510009027723
sip: [hidden email]
xmpp: [hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite max arguments assistance

Jungle Boogie
In reply to this post by Igor Tandetnik-2
Dear Igor,
--------------------------------------------
From: Igor Tandetnik <[hidden email]>
Sent:  Mon, 22 Sep 2014 16:34:18 -0400
To: [hidden email]
Subject: Re: [sqlite] sqlite max arguments assistance
>
>
> Just as I thought. You are storing your values as text - not as numbers - and
> comparing them accordingly, in alphabetical order.

Looking at our non-production database, the transaction_amount is stored as
numbers: NUMBER(19,4)

 
--
inum: 883510009027723
sip: [hidden email]
xmpp: [hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite max arguments assistance

Kees Nuyt
In reply to this post by Jungle Boogie
On Mon, 22 Sep 2014 14:02:57 -0700, Jungle Boogie
<[hidden email]> wrote:

> Igor Tandetnik <[hidden email]>
> 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 answer to this as I don't generate the data.
> Opening the csv file in Excel, I did have to change the transaction_amount
> column from "general" to "number". I think your assumptions are correct,
> though. I'll see if I can look at the values in the other database.
>
> I know the data is generated from a different database and a file is created,
> portalusemonthly.csv that's sent to a location where I can get it?

portalusemonthly.csv probably lists the amounts as
.....,"999.63",....
.....,"16695.36",....

and/or the receiving table doesn't define column
transaction_amount as a numeric type (REAL, NUMBER, INTEGER and
the like).
By the way, lacking a currency or decimal type, the best way to
represent money amounts is INTEGER, expressed as cents.

> Is there anything I can do post export from the other database to change the
> values correctly?

Have a look at
http://sqlite.org/datatype3.html
"2.3 Column Affinity Behavior Example"

--
Groet,

Kees Nuyt

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite max arguments assistance

Simon Slavin-3
In reply to this post by Jungle Boogie

On 22 Sep 2014, at 10:25pm, Jungle Boogie <[hidden email]> 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

<https://sqlite.org/cli.html>

If the table already exists (with that column defined as REAL) when you import, it will use the existing column definitions.

If you cannot change the file you are importing then you will get a row with the column names in, which you can, of course, ignore.  But it would probably be better to delete the first line if you can.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite max arguments assistance

Jungle Boogie
In reply to this post by Kees Nuyt
Dear Kees,
--------------------------------------------
From: Kees Nuyt <[hidden email]>
Sent:  Mon, 22 Sep 2014 23:59:52 +0200
To: [hidden email]
Subject: Re: [sqlite] sqlite max arguments assistance

>
> and/or the receiving table doesn't define column
> transaction_amount as a numeric type (REAL, NUMBER, INTEGER and
> the like).
> By the way, lacking a currency or decimal type, the best way to
> represent money amounts is INTEGER, expressed as cents.
>
>> Is there anything I can do post export from the other database to change the
>> values correctly?
>
> Have a look at
> http://sqlite.org/datatype3.html
> "2.3 Column Affinity Behavior Example"
>

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> .mode csv
sqlite> .import C:/work/somedata.csv tab
(with correct values)

Results in .schema assuming everything as text

So I'll just need to make transaction_amount number

 
--
inum: 883510009027723
sip: [hidden email]
xmpp: [hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite max arguments assistance

Simon Slavin-3

> On 23 Sep 2014, at 12:24am, Jungle Boogie <[hidden email]> 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> .mode csv
> sqlite> .import C:/work/somedata.csv tab
> (with correct values)
>
> Results in .schema assuming everything as text

because you defined them as text yourself.  You need to put your column affinities in the CREATE command.  Delete your existing table and try something more like this:

> create table august
> (MERCHANT_ID INTEGER, DBA TEXT, WHITELABEL_ID INTEGER, ORDER_ID INTEGER, TRANSACTION_DISPLAY_DATE TEXT, TYPE TEXT ,STATE TEXT, TRANSACTION_AMOUNT REAL);

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite max arguments assistance

Jungle Boogie
Dear Simon,
--------------------------------------------
From: Simon Slavin <[hidden email]>
Sent:  Tue, 23 Sep 2014 00:29:32 +0100
To: General Discussion of SQLite Database <[hidden email]>
Subject: Re: [sqlite] sqlite max arguments assistance

>
>
>> On 23 Sep 2014, at 12:24am, Jungle Boogie <[hidden email]> 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> .mode csv
>> sqlite> .import C:/work/somedata.csv tab
>> (with correct values)
>>
>> Results in .schema assuming everything as text
>
> because you defined them as text yourself.  You need to put your column affinities in the CREATE command.  Delete your existing table and try something more like this:
>
>> create table august
>> (MERCHANT_ID INTEGER, DBA TEXT, WHITELABEL_ID INTEGER, ORDER_ID INTEGER, TRANSACTION_DISPLAY_DATE TEXT, TYPE TEXT ,STATE TEXT, TRANSACTION_AMOUNT REAL);
>

Works perfectly!
sqlite> select max(transaction_amount) from august;
66882.4

I'll have some reading and playing around!

Thanks so much for everyone's assistance on this armature problem of mine.

> Simon.
--
inum: 883510009027723
sip: [hidden email]
xmpp: [hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite max arguments assistance

Keith Medcalf
In reply to this post by Jungle Boogie

Did you declare the field as containing numeric data, or a real?

'$999.63' > '$16695.36' is true (with or without the $sign)

999.63 > 16695.36 is false

looks like you are sorting text, not numbers.

>-----Original Message-----
>From: [hidden email] [mailto:sqlite-users-
>[hidden email]] On Behalf Of Jungle Boogie
>Sent: Monday, 22 September, 2014 13:12
>To: [hidden email]
>Subject: [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 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.
>
>
>For example, this record is much higher in terms of transaction_amount:
>$16695.36
>
>This is a csv file that I've imported and I'm using SQLiteSpy with sqlite
>3.8.6 as well as
>FreeBSD lyander-fbsd 10.0-RELEASE-p9 FreeBSD 10.0-RELEASE-p9 #0: Mon Sep
>15
>14:32:29 UTC 2014
>[hidden email]:/usr/obj/usr/src/sys/GENERIC  i386
>also with sqlite3.8.6
>
>
>How am i misunderstanding max?
>--
>inum: 883510009027723
>sip: [hidden email]
>xmpp: [hidden email]
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite max arguments assistance

Keith Medcalf
In reply to this post by Jungle Boogie

if there are no dollar signs, comma's, or other things that do not belong in numbers, then

UPDATE august SET transaction_amount = cast(transaction_amount, real)

>-----Original Message-----
>From: [hidden email] [mailto:sqlite-users-
>[hidden email]] On Behalf Of Jungle Boogie
>Sent: Monday, 22 September, 2014 15:03
>To: [hidden email]
>Subject: Re: [sqlite] sqlite max arguments assistance
>
>Dear Igor,
>--------------------------------------------
>From: Igor Tandetnik <[hidden email]>
>Sent:  Mon, 22 Sep 2014 16:34:18 -0400
>To: [hidden email]
>Subject: Re: [sqlite] sqlite max arguments assistance
>>
>>
>> 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 answer to this as I don't generate the
>data.
>Opening the csv file in Excel, I did have to change the
>transaction_amount
>column from "general" to "number". I think your assumptions are correct,
>though. I'll see if I can look at the values in the other database.
>
>I know the data is generated from a different database and a file is
>created,
>portalusemonthly.csv that's sent to a location where I can get it?
>
>Is there anything I can do post export from the other database to change
>the
>values correctly?
>
>--
>inum: 883510009027723
>sip: [hidden email]
>xmpp: [hidden email]
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
12