CODEWITHSUNDEEP
mysqlsqlselectsql-order-by
user1135693
user1135693

Reputation: 137

MySQL multiple column asc order

I am 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 returns results with only one column in ASC order.

Upvotes: 12

Views: 65820

Answers (8)

Aafaq Ahmad
Aafaq Ahmad

Reputation: 321

Use 

    ORDER BY title ASC,project_index ASC

instead of 

    ORDER BY title, project_index ASC;

give the order separately for both then it will work properly.

Upvotes: -1

IRSHAD
IRSHAD

Reputation: 2943

ORDER BY title ASC, project_index ASC;

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

AND you can add more columns like ORDER BY col1 ASC, col2 ASC, col3 DESC;

Upvotes: 3

Ashish Patel
Ashish Patel

Reputation: 1041

As per your requirement/query i think it is impossible to do ordering more than 2 columns in same table. If you want to order based on value you can do like this.

SELECT lat,lon, title, zip, city, state, region,cantone 
FROM company 
WHERE title != '' AND state IN(1,3,4,5,6,7,9,2) 
ORDER BY state=2,title asc

In above query it will first show all title in ascending order except state=2 and then shows all records with state=2 in last.

Upvotes: -1

php
php

Reputation: 4425

You try to sort both columns in ascending order. In mysql, you can use multiple order in a query. But the preference for the order by is very important here. First one get the most preference and next one get second preference.
That means, Your query is 

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

Where, order by title got first preference. The mysql will order the 'title' column in ascending order at first and display the result. Then only it will order 'project_index' column. So you cann't get answer as you want.

Upvotes: 0

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58491

Ascending order is the default for most (if not all) DBMS's so your statement is kind of weird in that respect but nevertheless, you can specify an order for each individual column by adding the specifier ASC or DESC to it.

Your statement then would become

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

Edit

As been mentioned by @Arvo & @Dems, currently you are sorting first on title and for identical titles on project_index. If you want your project_index sorted first, you have to place it first in the ORDER BY clause.

Your statement then becomes

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

and because ASC is the default sort order, you can omit them alltogether

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

Upvotes: 28

user3115056
user3115056

Reputation: 1280

You can try with the below and check--

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

Upvotes: -1

Saharsh Shah
Saharsh Shah

Reputation: 29071

Try this: 

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

Upvotes: 0

Ivaylo Petrov
Ivaylo Petrov

Reputation: 1172

If you are using mysql, check this out.

As they say there, you can use SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

Upvotes: 4

Related Questions