CODEWITHSUNDEEP
sqlitenumericsql-order-by
clement pignet
clement pignet

Reputation: 105

SQLITE order by numeric and not alphabetic

When I order my database SQLITE by Classement I have this :

Classement | Nom
1          | clem
10         | caro
11         | flo
12         | raph
2          | prisc
3          | karim
4          | prout

I would like to get :

Classement | Nom
1          | clem
2          | prisc
3          | karim
4          | prout
10         | caro
11         | flo
12         | raph

Here is my code :

SELECT t.Classement 
FROM tableau t 
WHERE 1 = (SELECT 1 + COUNT (*) FROM tableau t2 WHERE t2.Classement < t.Classement OR ( t2.Classement == t.Classement AND t2.Nom < t.Nom ))

Can anyone help me ? Thank you!

Upvotes: 8

Views: 3044

Answers (2)

johnzarifis
johnzarifis

Reputation: 382

I guess column Classement is not an integer but character. So try this:

SELECT * FROM tableau ORDER BY cast(Classement as integer);

Upvotes: 8

CL.
CL.

Reputation: 180172

You get alphabetic order if the values are strings.

To change the table so that all Classement values are numbers, ensure that the column type is not a text type, and use this:

UPDATE tableau SET Classement = CAST(Classement AS NUMBER);

Upvotes: 6

Related Questions