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.
Summary. Use the ORDER BY clause to sort the result set by one or more columns. Use the ASC option to sort the result set in ascending order and the DESC option to sort the result set in descending order. The ORDER BY clause is evaluated after the FROM and SELECT clauses.
Discussion: If you want to select records from a table but would like to see them sorted according to two columns, you can do so with ORDER BY . This clause comes at the end of your SQL query.
If you want to sort multiple columns specify all the columns by a comma. column1 DESC, column2 ASC; Here, the result is sorted by column1 in descending order first then, result is sorted by column2 in ascending order.
If you specify multiple columns, the result set is sorted by the first column and then that sorted result set is sorted by the second column, and so on. The columns that appear in the ORDER BY clause must correspond to either column in the select list or columns defined in the table specified in the FROM clause.
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
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With