`COLLATE` goes before the order direction:

    db.rawQuery(&quot;SELECT &quot; + catName 
               + &quot; FROM &quot; +tableName 
            +&quot; ORDER BY &quot;+catName+&quot; COLLATE NOCASE ASC;&quot;, null);

But you don&#39;t need the `ASC` -- that&#39;s the default so you could just as well use:


    db.rawQuery(&quot;SELECT &quot;+ catName 
                +&quot; FROM &quot;+ tableName 
            +&quot; ORDER BY &quot;+ catName +&quot; COLLATE NOCASE;&quot;, null);

add  `COLLATE NOCASE` after orderBy String.

    db.query(table, columns, selection,
            selectionArgs, groupBy, having,
            orderBy + &quot; COLLATE NOCASE ASC&quot;);

here, order by ASC or DESC depends on your need.


This should work too I think:

    db.rawQuery(&quot;SELECT &quot;+ catName 
            +&quot; FROM &quot;+ tableName 
        +&quot; ORDER BY lower(&quot;+ catName +&quot;);&quot;, null);

I have a multi-module app that is already on Github. It is comprised of two modules, one of them an Android app and the other a Rails based Web app. So my project&#39;s directory structure is in the form of:

    ProjectRoot
    |
    +-- web
    |
    +-- android
    |
    +-- .git

As such I cannot simply `cd` into ProjectRoot and push my app to Heroku as the root folder of the Rails app is `ProjectRoot/web`. Is there a way to push the `web` folder to Heroku? If I turn web into a git sub module, it should be easy to accomplish this, but then I only have 5 private repos on Git and I prefer to consume only 1 repo for my whole app.



How can I push a part of my git repo to Heroku?

I am learning Scala now and I want to write some silly little app like a console Twitter client, or whatever. The question is, how to structure application on disk and logically. I know python, and there I would just create some files with classes and then import them in the main module like `import util.ssh` or `from tweets import Retweet` (strongly hoping you wouldn&#39;t mind that names, they are just for reference). But how **should** I do this stuff using Scala? Also, I have not much experience with JVM and Java, so I am a complete newbie here.

Scala application structure

All I want to do is grab the stuff in alphabetical order and ignore the capital letters.

    db.rawQuery(&quot;SELECT &quot; + catName + &quot; FROM &quot;+tableName+&quot; ORDER BY &quot;+catName+&quot; ASC COLLATE NOCASE;&quot;, null);

This is the code I&#39;m using above, but it always gives me an SQLite exception saying that COLLATE is a syntax error.

&gt;android.database.sqlite.SQLiteException: near &quot;COLLATE&quot;: syntax error: , while compiling: SELECT Artist FROM testTable COLLATE NOCASE ASC


SQLite Query in non case sensitive alphabetical order

All I want to do is grab the stuff in alphabetical order and ignore the capital letters.
<pre><code class="hljs language-csharp">db.rawQuery("SELECT " + catName + " FROM "+tableName+" ORDER BY "+catName+" ASC COLLATE NOCASE;", null);
</code></pre>
This is the code I'm using above, but it always gives me an SQLite exception saying that COLLATE is a syntax error.
>android.database.sqlite.SQLiteException: near "COLLATE": syntax error: , while compiling: SELECT Artist FROM testTable COLLATE NOCASE ASC

Is there any way to set the height/width of a [LayoutParams][1] as density-independent pixels (dp)? It looks like the height/width, when set programmatically, are in pixels and not dp. 


  [1]: http://developer.android.com/reference/android/view/ViewGroup.LayoutParams.html

Programmatically set height on LayoutParams as density-independent pixels

I am using `SharedPreferences` in my android app. I am using both `commit()` and `apply()` method from shared preference. When I use AVD 2.3 it shows no error, but when I run the code in AVD 2.1, `apply()` method shows error. 

So what&#39;s the difference between these two? And by using only `commit()` can I store the preference value without any problem?

What&#39;s the difference between commit() and apply() in SharedPreferences

I have a dialog with EditText in it. The EditText is already populated when it is created.  When the user places the cursor on or near certain parts of the text a Toast will pop up.  

My problem is listening for changes in cursor position.  Another [post][1] asks the same question and the accepted solution was

&gt; You can override onSelectionChanged (int selStart, int selEnd) to get notified about selection changes. If the cursor is moved, this is called as well (in this case selStart == selEnd)


[onSelectionChanged (int selStart, int selEnd)][2] is a protected method of the TextView class.  How do override it?
  

  [1]: http://stackoverflow.com/questions/3652569/android-edittext-listener-for-cursor-position-change
  [2]: http://developer.android.com/reference/android/widget/TextView.html#onSelectionChanged%28int,%20int%29


Android EditText listener for cursor position change

I have a filename in my code as :

	String NAME_OF_FILE=&quot;//sdcard//imageq.png&quot;;
	FileInputStream fis =this.openFileInput(NAME_OF_FILE); // 2nd line

I get an error on 2nd line :
&gt; 05-11 16:49:06.355: ERROR/AndroidRuntime(4570): Caused by: java.lang.IllegalArgumentException: File //sdcard//imageq.png contains a path separator

I tried this format also:

	String NAME_OF_FILE=&quot;/sdcard/imageq.png&quot;;


java.lang.IllegalArgumentException: contains a path separator

I am using the `query` functions in order to build the SQL queries for my tables. Is there a way to see the actual query that is run? For instance log it somewhere?

So far the best I could do was to have a look at the cursor&#39;s member mQuery using a breakpoint. I&#39;d love to output the queries automatically though. This member is of course not public and does not have a getter.

---

Just for the record, here is an implementation of the accepted answer. 

    /**
     * Implement the cursor factory in order to log the queries before returning 
     * the cursor
     * 
     * @author Vincent @ MarvinLabs
     */
    public class SQLiteCursorFactory implements CursorFactory {
    
    	private boolean debugQueries = false;
    
    	public SQLiteCursorFactory() {
    		this.debugQueries = false;
    	}
    
    	public SQLiteCursorFactory(boolean debugQueries) {
    		this.debugQueries = debugQueries;
    	}
    
    	@Override
    	public Cursor newCursor(SQLiteDatabase db, SQLiteCursorDriver masterQuery, 
                                String editTable, SQLiteQuery query) {
    		if (debugQueries) {
    			Log.d(&quot;SQL&quot;, query.toString());
    		}
    		return new SQLiteCursor(db, masterQuery, editTable, query);
    	}
    }

Logging SQL queries in android

Is it possible to convert text into a number within MySQL query? I have a column with an identifier that consists a name and a number in the format of &quot;name-number&quot;. The column has VARCHAR type. I want to sort the rows according to the number (rows with the same name) but the column is sorted according to do character order, i.e.

    name-1
    name-11
    name-12
    name-2

If I cut off the number, can I convert the &#39;varchar&#39; number into the &#39;real&#39; number and use it to sort the rows? I would like to obtain the following order.

    name-1
    name-2
    name-11
    name-12

I cannot represent the number as a separate column.


**edited 2011-05-11 9:32**

I have found the following solution `... ORDER BY column * 1`. If the name will not contain any numbers is it safe to use that solution?

Convert partially non-numeric text into number in MySQL query

In my `posts` table, it saves timestamp for each post record.

What query can group posts by day, using this timestamp column?
 

Group by day from timestamp

My lecturer stated:

**All column names in SELECT list must appear in GROUP BY clause unless name is used only in an aggregate function**


I&#39;m just wanting some confirmation of this as I cannot think of a logical explanation as to why it should be true...



Do all columns in a SELECT list have to appear in a GROUP BY clause

I need to implement SQLite in my application. I followed this tutorial..
[Creating and using databases in Android one][1]

Everything is working fine. I inserted 1 row with 5 columns.
Now I want to update value of 1 column only and others will be remain same.

There is one update method in tutorial but it needs all the parameters, however I want to update only one column.


  [1]: http://www.android10.org/index.php/articlesdatastorage/235-creating-and-using-databases-in-android-one

Android SQLite: Update Statement

I am getting following error in my SQL server 2008 R2 database:

&gt; Cannot use a `CONTAINS` or `FREETEXT` predicate on table or indexed view &#39;tblArmy&#39; because it is not full-text indexed.



Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed

I&#39;m new to SQLite.  Is there a way I can export the results of a query into a CSV file?

SQLite: How do I save the result of a query as a CSV file?

I have three columns in an sqlite table:

        Column1    Column2    Column3
        A	       1	      1
        A	       1	      2
        A	       12	      2
        C	       13	      2
        B	       11	      2

I need to select `Column1-Column2-Column3` (e.g. `A-01-0001`). I want to pad each column with a `-`

I am a beginner with regards to SQLite, any help would be appreciated        



How to concatenate strings with padding in sqlite

Is it possible to alter table add MULTIPLE columns in a single statement in sqlite?
The following would not work.
&lt;pre&gt;
alter table test add column mycolumn1 text, add column mycolumn2 text;
&lt;/pre&gt;


sqlite alter table add MULTIPLE columns in a single statement

Ok I have a table with a indexed key and a non indexed field. 
I need to find all records with a certain value and return the row.
I would like to know if I can order by multiple values.

Example:

    id     x_field
    --     -----
    123    a
    124    a
    125    a
    126    b
    127    f
    128    b
    129    a
    130    x
    131    x
    132    b
    133    p
    134    p
    135    i

pseudo: would like the results to be ordered like this, `where ORDER BY x_field = &#39;f&#39;, &#39;p&#39;, &#39;i&#39;, &#39;a&#39;`

    SELECT *
    FROM table
    WHERE id NOT IN (126)
    ORDER BY x_field &#39;f&#39;, &#39;p&#39;, &#39;i&#39;, &#39;a&#39;

So the results would be:


    id     x_field
    --     -----
    127    f
    133    p
    134    p
    135    i
    123    a
    124    a
    125    a
    129    a

The syntax is valid but when I execute the query it never returns any results, even if I limit it to 1 record. Is there another way to go about this?

Think of the x_field as test results and I need to validate all the records that fall in the condition. I wanted to order the test results by failed values, passed values. So I could validate the failed values first and then the passed values using the ORDER BY.

What I can&#39;t do:

 - GROUP BY, as I need to return the specific record values
 - WHERE x_field IN(&#39;f&#39;, &#39;p&#39;, &#39;i&#39;, &#39;a&#39;), I need all the values as I&#39;m trying to use one query for several validation tests. And x_field values are not in DESC/ASC order

After writing this question I&#39;m starting to think that I need to rethink this, LOL! 
    
    

sql ORDER BY multiple values in specific order?

Is it possible to sort a set of related items in a DJango template?

That is: this code (with HTML tags omitted for clarity):

    {% for event in eventsCollection %}
       {{ event.location }}
       {% for attendee in event.attendee_set.all %}
         {{ attendee.first_name }} {{ attendee.last_name }}
       {% endfor %}
     {% endfor %}

displays ***almost*** exactly want I want.  The only thing I want to change is I the list of attendees to be sorted by last name.  I&#39;ve tried saying something like this:

    {% for event in events %}
       {{ event.location }}
       {% for attendee in event.attendee_set.order_by__last_name %}
         {{ attendee.first_name }} {{ attendee.last_name }}
       {% endfor %}
     {% endfor %}

Alas, the above syntax doesn&#39;t work (it produces an empty list) and neither does any other variation I have thought of (lot&#39;s of syntax errors reported, but no joy).   

I could, of course, produce some kind of array of sorted attendee lists in my view, but that is an ugly and fragile (and did I mention ugly) solution.

Needless to say, but I&#39;ll say it anyway, I have perused the on-line docs and searched Stack Overflow and the archives of django-user without finding anything helpful (ah, if only a query set were a dictionary dictsort would do the job, but it&#39;s not and it doesn&#39;t)

==============================================

Edited to add additional thoughts
after accepting Tawmas&#39;s answer.

-----------------------------------------------
Tawmas addressed the issue exactly as I presented it -- although the solution was not what I expected.  As a result I learned a useful technique that can be used in other situations as well. 

Tom&#39;s answer proposed an approach I had already mentioned in my OP and tentatively rejected as being &quot;ugly&quot;.  

The &quot;ugly&quot; was a gut reaction, and I wanted to clarify what was wrong with it.  In doing so I realized that the reason it was an ugly approach was because I was hung up on the idea of passing a query set to the template to be rendered.   If I relax that requirement, there is an un-ugly approach that should work.  

I haven&#39;t tried this yet, but suppose that rather than passing the queryset, the view code iterated through the query set producing a list of Events, then decorated each Event with a query set for the corresponding attendees which _WAS_ sorted (or filtered, or whatever) in the desired way.   Something like so:
 
    eventCollection = []   
    events = Event.object.[filtered and sorted to taste]
    for event in events:
       event.attendee_list = event.attendee_set.[filtered and sorted to taste]
       eventCollection.append(event)

Now the template becomes:

    {% for event in events %}
       {{ event.location }}
       {% for attendee in event.attendee_list %}
         {{ attendee.first_name }} {{ attendee.last_name }}
       {% endfor %}
     {% endfor %}


The downside is the view has to &quot;actualize&quot; all of the events at once which could be a problem if there were large numbers of events.  Of course one could add pagination, but that complicates the view considerably.  

The upside is the &quot;prepare the data to be displayed&quot; code is in the view where it belongs letting the template focus on formatting the data provided by the view for display. This is right and proper.
       
So my plan is to use Tawmas&#39; technique for large tables and the above technique for small
tables, with the definition of large and small left to the reader (grin.)
        


Sorting related items in a Django template

i have a table like this:

    name    date         time
    tom | 2011-07-04 | 01:09:52
    tom | 2011-07-04 | 01:09:52
    mad | 2011-07-04 | 02:10:53
    mad | 2009-06-03 | 00:01:01

i want oldest name first: 

    SELECT * 
    ORDER BY date ASC, time ASC 
    GROUP BY name

(-&gt;doesn&#39;t work!)

now it should give me first mad(has earlier date) then tom

but with `GROUP BY name ORDER BY date ASC, time ASC` gives me the newer mad first because it groups before it sorts!

again: the problem is that i can&#39;t sort by date and time before i group because GROUP BY must be before ORDER BY!


ORDER BY date and time BEFORE GROUP BY name in mysql

how can i use **union** and **order by** in mysql ?

    select * from _member_facebook 
    inner join _member_pts 
    ON _member_facebook._fb_owner=_member_pts._username 
    where _member_facebook._promote_point = 9 
    ORDER BY RAND() limit 2 
    UNION ALL
    select * from _member_facebook 
    inner join _member_pts 
    ON _member_facebook._fb_owner=_member_pts._username 
    where _member_facebook._promote_point = 8 limit 3

give me error 

    #1221 - Incorrect usage of UNION and ORDER BY

any one can help ?

Incorrect usage of UNION and ORDER BY?

This query 

    Message.where(&quot;message_type = ?&quot;, &quot;incoming&quot;).group(&quot;sender_number&quot;).count
     
will return me an hash.

    OrderedHash {&quot;1234&quot;=&gt;21, &quot;2345&quot;=&gt;11, &quot;3456&quot;=&gt;63, &quot;4568&quot;=&gt;100}

Now I want to order by count of each group. How can I do that within the query.



&quot;Order by&quot;  result of  &quot;group by&quot; count?

I imported a database with some data to compare with another database.

The target database has collation `Latin1_General_CI_AS` and the source database has `SQL_Latin1_General_CP1_CI_AS`.

I did change the collation of the source database in general to `Latin1_General_CI_AS` using the SQL Server Management Studio. But the tables and columns inside remains with the old collation.

I know that I can change a column using:

    ALTER TABLE [table] 
    ALTER COLUMN [column] VARCHAR(100) COLLATE Latin1_General_CI_AS

But I have to do this for all tables and all columns inside.

Before I know start to write a stored procedure that reads all tables and inside all column of type `varchar` and change them in a table and column cursor loop...

Does anyone know an easier way or is the only way to do this with a script running through all tables in a procedure?

Content Type	Original Author	Original Content on Stackoverflow
Question	Anthony Honciano	View Question on Stackoverflow
Solution 1 - Android	OMG Ponies	View Answer on Stackoverflow
Solution 2 - Android	minhazur	View Answer on Stackoverflow
Solution 3 - Android	dudeman	View Answer on Stackoverflow

SQLite Query in non case sensitive alphabetical order

Android Problem Overview

Android Solutions

Solution 1 - Android

Solution 2 - Android

Solution 3 - Android

Scala application structure

How can I push a part of my git repo to Heroku?

Attributions