• Welcome, Guest. Please login.
 
September 17, 2019, 09:52:08 am

News:

Welcome to the SQLitening support forums!


Alternative to max( x )

Started by Skip, April 24, 2014, 01:40:37 pm

Previous topic - Next topic

Skip

Hello All,

I have a table with 3 rows and 1 colum that is defined as text;  The value in the text field is blank, 1, and 2.
When I do a max( col ) I always get back a blank.  Is there a way, short of a loop to read all rows, to get back the
real max value of the column?

Thanks in advance -

Skip

Bern Ertl

I've written some TRIGGER code using MAX(x) and it works as expected.  The column it operates on is full of numerical values however.

Can you share your SQL statement employing the MAX(x) function?  Maybe there is something non-related causing the issue?


Bern Ertl

    BTW, SQLite documentation says:
Quotemax(X)   The max() aggregate function returns the maximum value of all values in the group. The maximum value is the value that would be returned last in an ORDER BY on the same column. Aggregate max() returns NULL if and only if there are no non-NULL values in the group.


Quote...
For the purposes of sorting rows, values are compared in the same way as for comparison expressions. The collation sequence used to compare two text values is determined as follows:[list=1]
  • If the ORDER BY expression is assigned a collation sequence using the postfix COLLATE operator, then the specified collation sequence is used.
  • Otherwise, if the ORDER BY expression is an alias to an expression that has been assigned a collation sequence using the postfix COLLATE operator, then the collation sequence assigned to the aliased expression is used.
  • Otherwise, if the ORDER BY expression is a column or an alias of an expression that is a column, then the default collation sequence for the column is used.
  • Otherwise, the BINARY collation sequence is used.

...


If the data in your table is stored as ' ', '1', '2' and not NULL, 1, 2 (ie. as text and not numbers), max(x) is going to return a result based upon the collation rules currently in effect.

cj


Without seeing the input values the blank is within a quote and 1, 2 are not.
The solution is to replace blanks with 0 so everything is binary and values are not quoted.

If everything is within quotes use leading zeros or spaces so string comparison is correct.
"0001"
"    "
"0002"

or all binary (do not enclose numbers within single quotes)
1
0
2

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
  DIM sArray(0) AS STRING
  slOpen "sample.db3","C"
  slEXE "drop table if exists test1;" +_
        "create table if not exists test1(f1 NOT NULL);" + _
        "insert into test1 values(2);"  +_
        "insert into test1 values(1);"  +_
        "insert into test1 values(' ')"
  slSelAry("select max(f1) from test1",sArray(),"Q")
  ? JOIN$(sArray(),$CRLF)
  'results:
  'max(f1)

  slEXE "drop table if exists test1;" +_
        "create table if not exists test1(f1 NOT NULL);" + _
        "insert into test1 values('2');"  +_
        "insert into test1 values('1');"  +_
        "insert into test1 values(' ')"   +_
        "insert into test  values('111')
  slSelAry("select max(f1) from test1",sArray(),"Q")
  ? JOIN$(sArray(),$CRLF)
  'results:
  'max(f1)
  ' 2
END FUNCTION

Bern Ertl

If the values in the table are a blank space, and two integers (1,2), the binary collation sequence will see them as:

1  -  00000001 (1)
2 -   00000010 (2)
space - 00100000  (32)

You have to be super careful not to mix string and nonstring data in the same column.

cj


Notice in this example only the SUM(F1) is what you want.

create table if not exists t1(f1);
insert into t1 values('003');
insert into t1 values('001');
insert into t1 values(6);
--------------------------------------------------
select * from t1 order by f1;   

001 
003 
--------------------------------------------------
select sum(f1) from t1;
10 
--------------------------------------------------
select max(f1) from t1;
003