CODEWITHSUNDEEP
sqliteaggregate-functionsmaxmin
eLearner
eLearner

Reputation: 85

SQLite min/max weird behavior

I have a rank table and its schema is defined as rank(id, key,value) where key is the primary key

sqlite> .schema rank
CREATE TABLE 'rank' ( ID VARCHAR, KEY VARCHAR, VALUE VARCHAR NOT NULL );
CREATE INDEX 'rank_id___djklaf3451jlZZZRFfa___' ON 'rank' ( ID );

sqlite> select * from rank;
-----------------------------------------------------
Tymb-W64uwvM|Tymc8LPQnxBg|5
TymdPRdFpBcE|TymdSsaIFhuI|2
TymdPRdFpBcE|TymdjkGgExcE|3
TymeVf6N1RH4|TymeZGxydCJ8|3
TymeVf6N1RH4|Tymeecz1ORW8|20

sqlite> select id,min(value) from rank group by id;
------------------------------------------------------
Tymb-W64uwvM|5
TymdPRdFpBcE|2
TymeVf6N1RH4|20

sqlite> select id,max(value) from rank group by id;
------------------------------------------------------
Tymb-W64uwvM|5
TymdPRdFpBcE|3
TymeVf6N1RH4|3

As you can see the 3rd result for both the min and max functions are incorrect. The sqlite version is 3.6.23.

Any advice?

Upvotes: 1

Views: 1879

Answers (2)

Stephen Quan
Stephen Quan

Reputation: 25956

It's because you've typed your 3rd parameter VARCHAR. The answer is 'correct' because it's VARCHAR sorting. If you had typed your 3rd parameter INTEGER, you would probably like the result much better. i.e. your CREATE TABLE statement should have been:

CREATE TABLE 'rank' ( ID VARCHAR, KEY VARCHAR, VALUE INTEGER NOT NULL );

Otherwise, if the VALUE must stay as VARCHAR you should try the following select statement to cast on the fly, but, as others have said, this is not great for performance:

SELECT ID, MIN(CAST(VALUE AS INTEGER)) FROM RANK GROUP BY ID;

Upvotes: 3

paxdiablo
paxdiablo

Reputation: 881423

It's because value is a character type (specifically varchar) rather than a numeric type.

If you're sorting character data, it comes out as:

2
20
3
3
5

which is what you'd see if you executed:

select value from rank order by value asc

If you want it to sort numerically, the column should be defined as a numeric type, such as integer.

Upvotes: 5

Related Questions