ORDER BY not giving max digital

Question

I'm using SQLite Studio and need to show 20 largsest numbers in SUM_D, but it get me "999,987" as biggest. At least i have "1 925,886", but it isn't show.

SELECT NAME, SUM_D FROM borg_1 ORDER BY SUM_D DESC LIMIT 20;

so here u could see my data and my outcome

my structure

Answer 1

Your problem is that you don't store numbers in the column SUM_D but strings.
Update the table so that all , are replaced with the . as the decimal separator and all spaces are removed:

UPDATE borg_1
SET SUM_D = REPLACE(REPLACE(SUM_D, ' ', ''), ',', '.')

Then your query will work fine.

Note that you should have defined the column's data type as REAL and not INT since you want to store floating point numbers, but this alone would not solve your problem.

See a simplified demo.

Answer 2

Try to use CAST expression:

SELECT NAME, SUM_D FROM borg_1 ORDER BY cast(replace(replace(SUM_D, ' ','' ),',','.') as NUMERIC) DESC LIMIT 20;

because sqlite can consider that SUM_D contains string value:

1. Because your value have while spaces.
2. You use , instead .

You have other option: format your numbers before inserting to DB.