You're assuming that the ordering is numeric, when the column is likely being interpreted as character data. You have to work at it a bit to get Sqlite to order numerically -- I don't offhand recall the tricks, but they're probably out there if you Google them. (Try "ORDER BY (sigma + 0)".)
(It may be that the values you're inserting are being seen as character. Eg, '-38.125' rather than -38.125.) On Dec 13, 7:31 pm, swgillan <swgil...@gmail.com> wrote: > Hello, > > I came across something really odd, that I just can't seem to figure > out. I am performing a query using rawQuery and also the query method > (just for consistency to test this problem). In both cases, when I > ORDER BY a column with negative numbers, the ASC and DESC appear to be > backwards (as if the column was taking the absolute value and then > ordering based on that). > > I am a little confused, and haven't found any other examples of it > happening. I am including the rawQuery and also the output using the > column with non-negative values (how I expect it to look), and the > same query with ordering by a column with negative numbers, which > clearly aren't sorted highest to lowest. > > SELECT * FROM w_local_data WHERE ap_id = 931 ORDER BY sigma DESC; // > highest to lowest based on sigma column > > _id cell_id ap_id mu > sigma > ---------- ---------- ---------- > ----------------- ---------------- > 157 994 931 -58.6315789473684 9.00999752506415 > 240 998 931 -57.1818181818182 7.15818897637437 > 318 1273 931 -73.9722222222222 5.59506782359403 > 141 993 931 -38.125 > 5.49857936198069 > 187 995 931 -66.5333333333333 5.40205722130211 > 205 996 931 -55.2592592592593 5.35822381151311 > 303 1001 931 -60.7391304347826 5.07523733695923 > 29 988 931 -45.7878787878788 5.03778195395659 > 52 989 931 -61.9655172413793 4.5370715043858 > 259 999 931 -71.2 > 4.53431361950185 > 75 990 931 -44.7878787878788 4.13233792332868 > 100 991 931 -56.6363636363636 3.62498218861398 > 286 1000 931 -72.1176470588235 3.39346002396611 > 5 987 931 -53.5454545454545 2.81867053435963 > > SELECT * FROM w_local_data WHERE ap_id = 931 ORDER BY mu DESC; // > highest to lowest based on mu column > > _id cell_id ap_id mu > sigma > ---------- ---------- ---------- > ----------------- ---------------- > 318 1273 931 -73.9722222222222 5.59506782359403 > 286 1000 931 -72.1176470588235 3.39346002396611 > 259 999 931 -71.2 > 4.53431361950185 > 187 995 931 -66.5333333333333 5.40205722130211 > 52 989 931 -61.9655172413793 4.5370715043858 > 303 1001 931 -60.7391304347826 5.07523733695923 > 157 994 931 -58.6315789473684 9.00999752506415 > 240 998 931 -57.1818181818182 7.15818897637437 > 100 991 931 -56.6363636363636 3.62498218861398 > 205 996 931 -55.2592592592593 5.35822381151311 > 5 987 931 -53.5454545454545 2.81867053435963 > 29 988 931 -45.7878787878788 5.03778195395659 > 75 990 931 -44.7878787878788 4.13233792332868 > 141 993 931 -38.125 > 5.49857936198069; > > Below is also a test I ran on my Master DB (MySQL) on my server that > had the same data in it. Here I am getting a return sorted correctly, > highest to lowest by negative numbers. > > SELECT cell_id, mu FROM w_venue_localization_data WHERE ap_id = 931 > ORDER BY mu DESC; > +---------+-------------------+ > | cell_id | mu | > +---------+-------------------+ > | 993 | -38.125 | > | 990 | -44.7878787878788 | > | 988 | -45.7878787878788 | > | 987 | -53.5454545454545 | > | 996 | -55.2592592592593 | > | 991 | -56.6363636363636 | > | 998 | -57.1818181818182 | > | 994 | -58.6315789473684 | > | 1001 | -60.7391304347826 | > | 989 | -61.9655172413793 | > | 995 | -66.5333333333333 | > | 999 | -71.2 | > | 1000 | -72.1176470588235 | > | 1273 | -73.9722222222222 | > +---------+-------------------+ > > If anyone can help shed some light on this for me, it would be much > appreciated. As a temporary workaround, I just use ASC as opposed to > DESC, but that seems a bit counter-intuitive when someone else looks > at the code. I have spent a good portion of my time searching on this, > but haven't come up with any explanation. At this point, it appears to > me to be a bug, but before going through steps to create a ticket I > thought I would ask around first to see if it was just something I did > wrong. > > Regards, > > Steven Gillan -- You received this message because you are subscribed to the Google Groups "Android Developers" group. To post to this group, send email to android-developers@googlegroups.com To unsubscribe from this group, send email to android-developers+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/android-developers?hl=en
