Re: [sqlite] SQL statement to get min/max values
Thank you Simon and Igor for suggesting the cast() - that works without me having to change anything! And of course I appreciate everyone's replies as well. Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL statement to get min/max values
"Dennis Volodomanov" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Funny enough, but it doesn't work on real data using v3.6.1... > > Here's the table: > > sqlite> .dump test_table > BEGIN TRANSACTION; > CREATE TABLE test_table (ID INTEGER PRIMARY KEY, ExternalID2 INTEGER, > ExternalID > INTEGER, Value ); > INSERT INTO "test_table" VALUES(1007,1,37,'-5'); > INSERT INTO "test_table" VALUES(1044,4,37,'-10'); > INSERT INTO "test_table" VALUES(1081,2,37,'-20'); > INSERT INTO "test_table" VALUES(1118,3,37,'-1'); > INSERT INTO "test_table" VALUES(1155,5,37,'-7'); > COMMIT; > sqlite> > > And here's the output: > > sqlite> select * from test_table; > 1007|1|37|-5 > 1044|4|37|-10 > 1081|2|37|-20 > 1118|3|37|-1 > 1155|5|37|-7 > sqlite> select min(Value) from test_table; > -1 > sqlite> select max(Value) from test_table; > -7 Well, '-7' comes lexicographically after '-1', no surprise here. By the same token, '5' would be greater than '10', since you insist on storing and comparing them as strings. Try select min(cast(Value as integer)), max(cast(Value as integer)) from test_table; Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL statement to get min/max values
Not sure about replacing the collation sequence - does not sound easier than recreating the table. You could just add a view: sqlite> CREATE TABLE test_table (ID INTEGER PRIMARY KEY, ...> ExternalID2 INTEGER, ...> ExternalID INTEGER, ...> Value ); sqlite> INSERT INTO "test_table" VALUES(1007,1,37,'-5'); sqlite> INSERT INTO "test_table" VALUES(1044,4,37,'-10'); sqlite> INSERT INTO "test_table" VALUES(1081,2,37,'-20'); sqlite> INSERT INTO "test_table" VALUES(1118,3,37,'-1'); sqlite> INSERT INTO "test_table" VALUES(1155,5,37,'-7'); sqlite> INSERT INTO "test_table" VALUES( 2044,4,37,'fred'); sqlite> INSERT INTO "test_table" VALUES( 3044,4,37,'bill'); sqlite> sqlite> create view test_view as ...> select ID, ExternalID2, ExternalID, cast( value as integer ) as Value ...> from test_table where cast( Value as text)=cast(Value as integer); sqlite> sqlite> select * from test_view; 1007|1|37|-5 1044|4|37|-10 1081|2|37|-20 1118|3|37|-1 1155|5|37|-7 sqlite> sqlite> select min( Value ) from test_view; -20 sqlite> select max( Value ) from test_view; -1 Rgds, Simon 2008/8/15 Dennis Volodomanov <[EMAIL PROTECTED]>: > >> Declaring the column as integer does not prevent you from storing strings: >> >> > Yes, except for a small problem of updating all live databases with the > new column type. I don't think I can update the column type without > recreating the table, right? It's not hard, so if it comes down to this, > then I guess I'll do it. Or if replacing the collation sequence is not > too hard, I'd rather go that route. > > Thank you, > > Dennis > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL statement to get min/max values
> Declaring the column as integer does not prevent you from storing strings: > > Yes, except for a small problem of updating all live databases with the new column type. I don't think I can update the column type without recreating the table, right? It's not hard, so if it comes down to this, then I guess I'll do it. Or if replacing the collation sequence is not too hard, I'd rather go that route. Thank you, Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL statement to get min/max values
Hi Dennis, Declaring the column as integer does not prevent you from storing strings: SQLite version 3.6.0 Enter ".help" for instructions sqlite> BEGIN TRANSACTION; sqlite> CREATE TABLE test_table (ID INTEGER PRIMARY KEY, ExternalID2 INTEGER, ...> ExternalID ...> INTEGER, Value INTEGER); sqlite> INSERT INTO "test_table" VALUES(1007,1,37,'-5'); sqlite> INSERT INTO "test_table" VALUES(1044,4,37,'-10'); sqlite> INSERT INTO "test_table" VALUES(1081,2,37,'-20'); sqlite> INSERT INTO "test_table" VALUES(2081,2,37,'fred'); sqlite> INSERT INTO "test_table" VALUES(3081,2,37,'bill'); sqlite> INSERT INTO "test_table" VALUES(1118,3,37,'-1'); sqlite> INSERT INTO "test_table" VALUES(1155,5,37,'-7'); sqlite> COMMIT; sqlite> sqlite> sqlite> sqlite> select max( value ) from test_table; fred sqlite> select min( value ) from test_table; -20 sqlite> select *, typeof( value ) from test_table; 1007|1|37|-5|integer 1044|4|37|-10|integer 1081|2|37|-20|integer 1118|3|37|-1|integer 1155|5|37|-7|integer 2081|2|37|fred|text 3081|2|37|bill|text sqlite> select max( value ) from test_table where typeof( value ) = 'integer'; -1 sqlite> Rgds, Simon 2008/8/15 Dennis Volodomanov <[EMAIL PROTECTED]>: > >> Works just fine with 3.6.1 if you declare the Value column to be >> INTEGER. As it is, I have no idea what collation is used, but the >> Value column will be declared to default to TEXT values, as shown by >> >> select typeof(value) from test_table; >> > I haven't tried that, but I cannot declare it as INTEGER, because it > contains strings as well (of course they're not selected to get min/max > values). Would I need to write my own min/max functions to handle this? > > Thanks, > > Dennis > > > P.S. Sorry for the message I sent to you personally - the Reply-to > button is too smart :) > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL statement to get min/max values
> Works just fine with 3.6.1 if you declare the Value column to be > INTEGER. As it is, I have no idea what collation is used, but the > Value column will be declared to default to TEXT values, as shown by > > select typeof(value) from test_table; > I haven't tried that, but I cannot declare it as INTEGER, because it contains strings as well (of course they're not selected to get min/max values). Would I need to write my own min/max functions to handle this? Thanks, Dennis P.S. Sorry for the message I sent to you personally - the Reply-to button is too smart :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL statement to get min/max values
Dennis Volodomanov wrote: Funny enough, but it doesn't work on real data using v3.6.1... Here's the table: sqlite> .dump test_table BEGIN TRANSACTION; CREATE TABLE test_table (ID INTEGER PRIMARY KEY, ExternalID2 INTEGER, ExternalID INTEGER, Value ); INSERT INTO "test_table" VALUES(1007,1,37,'-5'); INSERT INTO "test_table" VALUES(1044,4,37,'-10'); INSERT INTO "test_table" VALUES(1081,2,37,'-20'); INSERT INTO "test_table" VALUES(1118,3,37,'-1'); INSERT INTO "test_table" VALUES(1155,5,37,'-7'); COMMIT; sqlite> And here's the output: sqlite> select * from test_table; 1007|1|37|-5 1044|4|37|-10 1081|2|37|-20 1118|3|37|-1 1155|5|37|-7 sqlite> select min(Value) from test_table; -1 sqlite> select max(Value) from test_table; -7 I'm confused :) Thanks, Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Works just fine with 3.6.1 if you declare the Value column to be INTEGER. As it is, I have no idea what collation is used, but the Value column will be declared to default to TEXT values, as shown by select typeof(value) from test_table; -- Multumesc, Mihai Limbasan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL statement to get min/max values
Funny enough, but it doesn't work on real data using v3.6.1... Here's the table: sqlite> .dump test_table BEGIN TRANSACTION; CREATE TABLE test_table (ID INTEGER PRIMARY KEY, ExternalID2 INTEGER, ExternalID INTEGER, Value ); INSERT INTO "test_table" VALUES(1007,1,37,'-5'); INSERT INTO "test_table" VALUES(1044,4,37,'-10'); INSERT INTO "test_table" VALUES(1081,2,37,'-20'); INSERT INTO "test_table" VALUES(1118,3,37,'-1'); INSERT INTO "test_table" VALUES(1155,5,37,'-7'); COMMIT; sqlite> And here's the output: sqlite> select * from test_table; 1007|1|37|-5 1044|4|37|-10 1081|2|37|-20 1118|3|37|-1 1155|5|37|-7 sqlite> select min(Value) from test_table; -1 sqlite> select max(Value) from test_table; -7 I'm confused :) Thanks, Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL statement to get min/max values
I've got 3.6.0 and it works fine here On Thu, Aug 14, 2008 at 9:09 PM, Dennis Volodomanov <[EMAIL PROTECTED]> wrote: >> I'm using 3.3.5, I'll get the latest and see if works there or not in > a >> few minutes. > > Ok, it works in 3.6.1 - sorry for the troubles... I'll just upgrade the > program then. > > Thank you, > > Dennis > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL statement to get min/max values
> I'm using 3.3.5, I'll get the latest and see if works there or not in a > few minutes. Ok, it works in 3.6.1 - sorry for the troubles... I'll just upgrade the program then. Thank you, Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL statement to get min/max values
> Could it be that since you're not defining a type for Data it assumes > string? > Try creating the table with > id integer, externalid integer, data number (or numeric) That Data column could contain anything (int, double, string), it'll be up to the application's logic to only get Data for ExternalIDs that are numeric. I've confirmed it to work in 3.6.1 though - it doesn't work in 3.3.5. Thanks, Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL statement to get min/max values
Could it be that since you're not defining a type for Data it assumes string? Try creating the table with id integer, externalid integer, data number (or numeric) On Thu, Aug 14, 2008 at 7:00 PM, Dennis Volodomanov <[EMAIL PROTECTED]> wrote: > Hello all, > > I've tried a few SQL statements, but can't seem to get it to work > properly, so I'd like to ask your help. > > Suppose I have a table like this: > > CREATE TABLE test_table (ID INTEGER PRIMARY KEY, ExternalID, Data); > > And some contents: > > 1| 2| -7 > 2| 2| 5 > 3| 1| 0 > 4| 2| -20 > 5| 2| -5 > 6| 2| 1 > 7| 1| 10 > > Now, what I'd like to do is get minimum (-20) and maximum (5) from the > table where ExternalID=2. It must be very simple, but I can't seem to > get the correct result. What I tried was: > > SELECT min(Data) FROM (SELECT Data FROM test_table WHERE ExternalID=2); > > But the above doesn't return the expected result. > > Thanks for your help! > > Dennis > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL statement to get min/max values
> What version of SQLite are you using? I'm using the 3.5.7 version that > came > with OS X 10.5, and I get -2 as expected. I'm using 3.3.5, I'll get the latest and see if works there or not in a few minutes. > Also, what's with the superfluous subquery? Why not just say > > SELECT max(Data) FROM test_table WHERE ExternalID=2; > > You can even do min and max at the same time: > > SELECT min(Data), max(Data) FROM test_table WHERE ExternalID=2; Yes, that query is a remnant of things I tried, because I couldn't get the values to return as expected. Your second query looks perfect for my needs, thanks! Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL statement to get min/max values
On Thu, Aug 14, 2008 at 9:45 PM, Dennis Volodomanov < [EMAIL PROTECTED]> wrote: > > Seems to work ok for me. What values were you expecting? > > Yes, that works. Bad example on my part, sorry. > > What doesn't work is this: > > 1|2|-7 > 2|2|-5 > 3|2|-20 > 4|2|-5 > 5|2|-2 > > SELECT max(Data) FROM (SELECT Data FROM test_table WHERE ExternalID=2); > > This returns a -5, while I'm expecting a -2. > > Thank you, What version of SQLite are you using? I'm using the 3.5.7 version that came with OS X 10.5, and I get -2 as expected. Also, what's with the superfluous subquery? Why not just say SELECT max(Data) FROM test_table WHERE ExternalID=2; You can even do min and max at the same time: SELECT min(Data), max(Data) FROM test_table WHERE ExternalID=2; Or get real fancy: create view test_stats as select ExternalId, max(Data) as maxData, min(Data) as minData, avg(Data) as avgData from test_table group by ExternalId -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL statement to get min/max values
> Seems to work ok for me. What values were you expecting? Yes, that works. Bad example on my part, sorry. What doesn't work is this: 1|2|-7 2|2|-5 3|2|-20 4|2|-5 5|2|-2 SELECT max(Data) FROM (SELECT Data FROM test_table WHERE ExternalID=2); This returns a -5, while I'm expecting a -2. Thank you, Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL statement to get min/max values
On Fri, Aug 15, 2008 at 1:00 AM, Dennis Volodomanov <[EMAIL PROTECTED]> wrote: > Suppose I have a table like this: > > CREATE TABLE test_table (ID INTEGER PRIMARY KEY, ExternalID, Data); > > And some contents: > > 1| 2| -7 > 2| 2| 5 > 3| 1| 0 > 4| 2| -20 > 5| 2| -5 > 6| 2| 1 > 7| 1| 10 > > Now, what I'd like to do is get minimum (-20) and maximum (5) from the > table where ExternalID=2. It must be very simple, but I can't seem to > get the correct result. What I tried was: > > SELECT min(Data) FROM (SELECT Data FROM test_table WHERE ExternalID=2); > > But the above doesn't return the expected result. - SQLite version 3.5.9 Enter ".help" for instructions sqlite> create table test_table (ID INTEGER PRIMARY KEY, ExternalID, Data); sqlite> insert into test_table values (1,2,-7); sqlite> insert into test_table values (2,2,5); sqlite> insert into test_table values (3,1,0); sqlite> insert into test_table values (4,2,-20); sqlite> insert into test_table values (5,2,-5); sqlite> insert into test_table values (6,2,1); sqlite> insert into test_table values (7,1,10); sqlite> select * from test_table; 1|2|-7 2|2|5 3|1|0 4|2|-20 5|2|-5 6|2|1 7|1|10 sqlite> SELECT min(Data) FROM (SELECT Data FROM test_table WHERE ExternalID=2); -20 sqlite> SELECT max(Data) FROM (SELECT Data FROM test_table WHERE ExternalID=2); 5 sqlite> SELECT max(Data) from test_table WHERE ExternalID=2; 5 sqlite> SELECT min(Data) from test_table WHERE ExternalID=2; -20 - Seems to work ok for me. What values were you expecting? Regards, ~Nuno Lucas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL statement to get min/max values
Hello all, I've tried a few SQL statements, but can't seem to get it to work properly, so I'd like to ask your help. Suppose I have a table like this: CREATE TABLE test_table (ID INTEGER PRIMARY KEY, ExternalID, Data); And some contents: 1| 2| -7 2| 2| 5 3| 1| 0 4| 2| -20 5| 2| -5 6| 2| 1 7| 1| 10 Now, what I'd like to do is get minimum (-20) and maximum (5) from the table where ExternalID=2. It must be very simple, but I can't seem to get the correct result. What I tried was: SELECT min(Data) FROM (SELECT Data FROM test_table WHERE ExternalID=2); But the above doesn't return the expected result. Thanks for your help! Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
