You could also make sure that your entire database is "consistently rounded" by updating it to consistent rounding before running your original query: update latiSaltxiīefore using your original query, choosing the rounding precision as appropriate for you to ensure that all the numbers are "similar representations".Īn IEEE floating point number is only an approximation with certain guarantees as to the precision and accuracy of the approximation. Select westccod, round(lati10, 6) as lati10, round(longgi,6) as longi Select westccod, round(lati10, 6), round(longi, 6) Order by round(lati10, 6), round(longi, 6) Where round(lati10, 6) between 41.0 and 41.3 Example: select westccod, round(lati10, 6) as lati10, round(longi, 6) as longi The order by and conditions should be rounded to the precision you want to see. As I've explained when request lati10 >= 41.2 the order is correct.Ĭorrect. On the other side it makes no sense that the order changes depending on the filter. Integer|408|real|41.2|real|129.727571723508Īlso by examining the dump, alfanumerics are written with quotes which where not found. I can provide the particular data if needed (~ 1 MB 7zipped)Īll between integer and real, actually lati10 a mixture of both integer|392|integer|41|real|140.936371 It is very strange, probably I am missing something but right now I cannot figure out what. This behavior happens with any sqlite version (last tested 3.31.1) Then the result is sorted! 840|41.2|-73.970001ĭumping the database and building a new one with sqlite3.exe does not help. The following select should result in records sorted first by lati10 and then longi SELECT * A database containing just one table with following schema CREATE TABLE latiSaltxi (westCCod, lati10, longi)
0 kommentar(er)
