MySQL implementation method of multi-column ascending sort

Question

I'm trying to run this query in ascending order:

SELECT title,project_index
FROM projectdetail
WHERE project_index BETWEEN 1 AND 6
ORDER BY title, project_index ASC;

I need two columns in ascending order, but the above query only returns results for one column in ASC order.

Answer 1

Ascending order is the default sorting mode for most (if not all) DBMS, so your statement is a bit strange in that regard, but anyway, you can specify the sorting mode by adding ASC or DESC on each column.

Your statement will become:

SELECT  title
        , project_index 
FROM    projectdetail 
WHERE   project_index BETWEEN 1 AND 6 
ORDER BY 
        title ASC
        , project_index ASC

edit

As @Arvo and @Dems mentioned, you are currently sorting by title first, then by project_index if the titles are the same. If you want project_index to be sorted first, you must put it first in the ORDER BY clause.

Your statement will become:

SELECT  title
        , project_index 
FROM    projectdetail 
WHERE   project_index BETWEEN 1 AND 6 
ORDER BY 
        project_index ASC
        , title ASC

Because ASC is the default sort order, you can omit them:

SELECT  title
        , project_index 
FROM    projectdetail 
WHERE   project_index BETWEEN 1 AND 6 
ORDER BY 
        project_index
        , title