3.3.4.4 Sorting Rows

You may have noticed in the preceding examples that the result
rows are displayed in no particular order. It is often easier
to examine query output when the rows are sorted in some
meaningful way. To sort a result, use an ORDER
BY clause.

On character type columns, sorting—like all other
comparison operations—is normally performed in a
case-insensitive fashion. This means that the order is
undefined for columns that are identical except for their
case. You can force a case-sensitive sort for a column by
using BINARY like so:
ORDER BY BINARY
col_name.

The default sort order is ascending, with smallest values
first. To sort in reverse (descending) order, add the
DESC keyword to the name of the column you
are sorting by:

You can sort on multiple columns, and you can sort different
columns in different directions. For example, to sort by type
of animal in ascending order, then by birth date within animal
type in descending order (youngest animals first), use the
following query:

We must make a Query, turning the numeric(integer) field to text andordered at this last one, Example:Query: SELECT left(numbers, 11) as numbersSTR from table order by numbersSTRWe have as result the following:

"If a PRIMARY KEY or UNIQUE index consists of only one column that has an integer type, you can also refer to the column as _rowid in SELECT statements."

Posted by
Linh Hoang
on
February 11, 2007

Follow up with Edward Lipchus's explanation:

field(para1, para2, para3, para4 .....)

paramenter 1 is the column's name. Parameter 2 and onward are the ranking you would like. So 'cat' would be the highest rank and would be listed before 'dog'. Other unspecified ranks, however, would be appeared before everything else, like 'snake'. If you want unspecified rank to be displayed last, just do an inverse of the field's ranking parameters.

Please mind the methods described here for sorting a mixture of numbers and letters are helpful only in some case. The following case would be harder to sort:1,2,10,x1,x2,x2a,x10,yy1,yy1a,yy2,yy10

My guess is that you'll need a not so simple function (or expression) to sort the letter prefix, then the numbers inside that prefix and then the suffix. If you've already done something of the like, feel free to post it here.

Posted by
Ari El
on
November 19, 2009

This solved a problem with "natural" sort:

SELECT * FROM table_1 ORDER BY CAST(mid(c1, 6, LENGTH(c1) -5) AS unsigned);

change "table_1" and "c1" to your fields, respectively.

We got:

123T3T456...

Posted by
Bill CreateSource
on
May 29, 2010

An easy method of allowing user-selectable ordering of items is with an integer int/tinyint field:

sequence tinyint(3) default 0

The default behavior will always put zero's first. Some users may find this confusing, "I set it to 1 and it should be number 1." In these instances you can do this:

select * from test2 order by sequence>0 desc, sequence asc, title asc;

Note how the secondary order of title arranges items alphabetically if no sequence is assigned.

Posted by
gaurav kaushik
on
November 10, 2010

hey Mr. duck and Mr. roberto what u ppl are saying....if u r giving this command :select * from pet order by field(species, 'bird', 'dog', 'cat') desc;then cat will be having the lowest priority will appear first....in ascending order bird will appear first