ORDER BY on different columns in different directions in SQLite

Question

I have a table defined by:

 CREATE TABLE bar_table (
  _id INTEGER NOT NULL PRIMARY KEY,
  index INTEGER NOT NULL DEFAULT '65535',
  _date DATE
 )

My basic select statement is:

SELECT * FROM bar_table ORDER BY <your-clause-here>

How do I order my selection by index ascending, and date descending? i.e. small indexes come before large indexes. In the event that two indexes are the same, the later date is to come first.

The documentation is pointing me towards COLLATion, ubut I'm not really sure what that is.

Answer 1

While I know that you already have your own answer up here, I think it's pertinent to go into the details at work here.

First, the order by clause goes in order of columns or expressions specified. In this case:

order by index asc, _date desc

That sorts by index smallest to largest (ascending), and then _date largest to smallest (descending). While asc is the default value, I generally include it when I have multiple columns going opposite directions, like you do here.

You can also include expressions in your order by:

order by case when index < 0 then 1 else 0 end desc, _date desc

This would put all of the negative index rows at the top, and then sort those by _date. Using expressions in your order by clause is very effective in certain circumstances.

Now, you mentioned collation, and a little confusion as to what that is. Collation is how the database treats capital and accents in string comparisons. With a Captial-Sensitive collation, 'abc' != 'ABC'. However, with a Captial-Insensitive collation, 'abc' = 'ABC'.

It should be noted that collation is not a character set. That's usually determined by data type (varchar == ASCII, nvarchar == Unicode). Collation determines how strings are compared, not what character sets are available for use.

Moreover, collation is also important with certain languages. Given a Latin collation, you just have to worry about capitalization and accents, but given a Danish collation, 'aa' = 'å'.¹ So you can see that collation plays a big part in determining sorting and comparisons for different languages, as well.

Collation is very important when ordering, because it determines how strings will be ordered given different capitalizations and accents. That's why it keeps coming up in your searches. Collation is important, and it even affected StackOverflow this week!

¹_{: Thanks to Michael Madsen for pointing out this specific example.}