SQLite ORDER BY syntax

Put your problem here if it does not fit any of the other categories.

SQLite ORDER BY syntax

Postby ramgraph1 » Mon Feb 18, 2008 9:50 am

The Android SQLite docs say about the order by section of a cursor query:
How to order the rows, formatted as an SQL ORDER BY clause (excluding the ORDER BY itself). Passing null will use the default sort order, which may be unordered.


But I can't figure out how to use this. I tried this code:

Code: Select all
    public Cursor fetchAllNotes() {
        return mDb.query(DATABASE_TABLE, new String[] {
                KEY_ROWID, KEY_username, KEY_comment}, null, null, null, null, Key_username DESC);
    }


and I tried it with the "ORDER BY" (though the text above seems to mean it should not be excluded)
Code: Select all
return mDb.query(DATABASE_TABLE, new String[] {
                KEY_ROWID, KEY_username, KEY_comment}, null, null, null, null, ORDER BY Key_username DESC);
    }


But neither way worked. What code do I need to use here to get the info ordered by KEY_username in descending order?
Thanks for any help you can give.
ramgraph1
Experienced Developer
Experienced Developer
 
Posts: 68
Joined: Wed Jan 09, 2008 10:03 pm

Top

Postby plusminus » Mon Feb 18, 2008 10:26 am

Hello ramgraph1,

You need to concat the strings.
As a full SQL string ot would be: "... ORDER BY username DESC", but we need to exclude the "ORDER BY".
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.     public Cursor fetchAllNotes() {
  2.         return mDb.query(DATABASE_TABLE, new String[] {
  3.                 KEY_ROWID, KEY_username, KEY_comment}, null, null, null, null, Key_username + " DESC");
  4.     }
Parsed in 0.031 seconds, using GeSHi 1.0.8.4


(untested)

Regards,
plusminus
Image
Image | Android Development Community / Tutorials
User avatar
plusminus
Site Admin
Site Admin
 
Posts: 2688
Joined: Wed Nov 14, 2007 8:37 pm
Location: Schriesheim, Germany

Postby ramgraph1 » Mon Feb 18, 2008 10:28 pm

That did it! Thanks.
ramgraph1
Experienced Developer
Experienced Developer
 
Posts: 68
Joined: Wed Jan 09, 2008 10:03 pm

Postby jonaaathan » Wed Jul 22, 2009 6:18 am

I'm having problems sorting numbers with sqlite. I'm hoping someone can help me..

Problem: I have a list of numbers that need to be ordered from highest to lowest. This is the code I use to sort the list..

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1.  
  2.         public Cursor retrieveAll() {
  3.  
  4.                 return sqliteDatabase.query(SQL_TABLE_NAME, new String[] { COL_ROW_ID,
  5.  
  6.                                  COL_USERNAME, COL_SCORE}, null, null,
  7.  
  8.                                 null, null, COL_SCORE  + " DESC");
  9.  
  10.         }
  11.  
  12.  
Parsed in 0.045 seconds, using GeSHi 1.0.8.4


When the list is displayed, it gives the values in this order: 9, 88, 50, 42, 3, 25, 11

How do I make it so that it gets sorted like this : 88, 50, 42,25,11,9,3 ?
jonaaathan
Freshman
Freshman
 
Posts: 5
Joined: Wed Jun 17, 2009 4:46 am

Postby nmc » Wed Jul 22, 2009 10:04 am

When the list is displayed, it gives the values in this order: 9, 88, 50, 42, 3, 25, 11

How do I make it so that it gets sorted like this : 88, 50, 42,25,11,9,3 ?


Just put your numbers in a field of type integer (instead f type text) :wink:

If that isnt possible, you could use
order by cast(<columnName> as integer)
nmc
Senior Developer
Senior Developer
 
Posts: 154
Joined: Thu Nov 27, 2008 8:30 pm
Location: Germany

Postby jonaaathan » Wed Jul 22, 2009 5:24 pm

Just put your numbers in a field of type integer (instead f type text)


That did the trick, thanks so much!
jonaaathan
Freshman
Freshman
 
Posts: 5
Joined: Wed Jun 17, 2009 4:46 am

Top

Postby mebibou » Fri Nov 06, 2009 9:49 pm

Hey everyone,

I have exactly the same problem, and I cannot figure out how to solve it.
My field that I want to ORDER BY is defined as an integer, and I also tried to cast it in the ORDER BY clause, but it still give me the rows in the order that I added them.

Does anyone had the same problem and solved it ? it is really important.

Thanks
mebibou
Once Poster
Once Poster
 
Posts: 1
Joined: Fri Nov 06, 2009 7:30 pm

Postby kawaiijetta » Thu Feb 11, 2010 8:41 pm

My problem is similar, but using ORDER BY and LIMIT in an update... basically I want to update only the first N rows by date. SQLite documentation says I can do this, according to the SQLite.org syntax diagram here:

SQLite Syntax Diagram

But when I try the following query in Android:

Code: Select all
db.execSQL("UPDATE mytable SET status=1 WHERE status=0 ORDER BY creationdate ASC LIMIT 100");


I get an exception complaining about the ORDER BY. Any ideas? Is this part of SQLite not supported by Android?
kawaiijetta
Freshman
Freshman
 
Posts: 7
Joined: Wed Jan 06, 2010 4:04 pm

Top

Return to Other Coding-Problems

Who is online

Users browsing this forum: Exabot [Bot] and 14 guests