CODEWITHSUNDEEP
c#sqliteaggregate-functionsmaxmin
user1320852
user1320852

Reputation: 97

Sqlite min,max return wrong result in ado.net

i am using ADO.NET Data Provider for SQLite: 1.0.82.0

Aggregate function returns wrong result:

###Table structure###
CREATE TABLE ta85 (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, date INTEGER, _90 REAL, _100 REAL)

###Rows: ###
select * from ta85

id, date, _90, _100

"1","1355480577920","4,4","2"

"2","1355480877929","15","5,2"

"3","1355481177937","26,4","9,4"

------------------

Now if i want to select the max, min values for each column:

SELECT date,MIN(_90), MAX(_90),MIN(_100), MAX(_100) FROM ta85 WHERE date between 1355480296794 and 1355481366266

returns:

"1355481177937","15","4,4","2","9,4"

which for column _100 is correct but for column _90 is wrong.

Solution

Thanks for all your answers. you were both right.

cmd.Parameters.AddWithValue(columname,"");

to

cmd.Parameters.AddWithValue(columname,0.0d);

Upvotes: 1

Views: 514

Answers (2)

Jordão
Jordão

Reputation: 56477

You're using the wrong kind of literal to insert floating point numbers into the database, and you're getting string comparisons. Use dots instead of commas.

Remember that SQLite is very loose with types:

SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container.

SQL Fiddle here.

Upvotes: 1

dmaij
dmaij

Reputation: 1007

It appears you are comparing strings instead of numbers. Perhaps format your number as '4.4' instead of the '4,4'

Upvotes: 0

Related Questions