Yeah, remember that Sqlite doesn't really pay any attention to the declared column types, but types each individual data cell separately. You have to use care on insertion (avoid quotes for numerics, eg) to get the data type you expect, or else you have to arrange your queries to coerce the data type when ordering. This is a well-documented "feature" of Sqlite and perhaps the biggest potential incompatibility vs other SQL implementations.
On Dec 13, 7:57 pm, swgillan <swgil...@gmail.com> wrote: > Thanks Dan, > > That is actually what I was thinking, but wanted to check here as > well. I will test with your suggestion tomorrow. > > On Dec 13, 5:46 pm, DanH <danhi...@ieee.org> wrote: > > > 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
