Python Forum
Sqlite3 help with descending order - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Sqlite3 help with descending order (/thread-18431.html)

Sqlite3 help with descending order - I_Am_Groot - May-17-2019

Hi,
I am tring to use sqlite3 in python. So far I have got it working and can display values. However, when I try displaying it in descending order, it works with 1 digit numbers, but when a value has 2 digits, it will only count the first digit.
This is the code I use to display the values:
for row in c.execute('SELECT * FROM scores ORDER BY score1 DESC LIMIT 5'):
        print (row)
The output ends up being:
Output:
('Michael', '8')
('Jim', '2')
('Tim', '2')
('James', '2')
('Chris', '19')
However, I want it to put the highest numbers at the top and not just take the first digit into account.

Thanks.

RE: Sqlite3 help with descending order - buran - May-17-2019

Your "numbers" are stored as TEXT. So it is sorted as text. You either need to change the field type. Or if you HAVE TO store value as text (why?), cast it to INTEGER when query

RE: Sqlite3 help with descending order - I_Am_Groot - May-17-2019

(May-17-2019, 06:45 AM)buran Wrote: Your "numbers" are stored as TEXT. So it is sorted as text. You either need to change the field type. Or if you HAVE TO store value as text (why?), cast it to INTEGER when query


Thank you,

It worked when I created a new table and made it REAL instead of TEXT. However, now it prints it like:
('Jim', 19.0)
('Mark', 3.0)

Do you know how to get it to print without the decimal?
Thanks.

RE: Sqlite3 help with descending order - buran - May-17-2019

Make it INTEGER, not REAL

RE: Sqlite3 help with descending order - I_Am_Groot - May-18-2019

Thanks for all your help buran. This made it work. :)