Quick Sorting Method in SQL Query for Disorderly Sorting Criterion

ORDER BY can be used for orderly sorting criterion only. For disorderly sorting criterion, such as Car, Ship, Airplane, ORDER BY could not accomplish the task. Here is a quick method to use ORDER BY as usually.

Step One: Add order number in front of sorting field by using concatenation function.

Step Two: Using ORDER BY as usually.
Step Three: Using substring function to cut the first character.

Example in T-SQL:

SELECT SUBSTRING(newSort, 2, 8)
, Other_Fields
FROM
(
SELECT CASE WHEN Sort_Field = ‘Car’ THEN ‘1Car’
WHEN Sort_Field = ‘Ship’ THEN ‘2Ship’
WHEN Sort_Field = ‘Airplane’ THEN ‘3Airplane’ END AS newSort
, Other_Fields
FROM Tab
) AS innerQuery
ORDER BY newSort

This method can be used in T-SQL, PL/SQL, etc. For T-SQL, please pay special notice to this: ORDER BY cannot be used for any inner query, it must be placed at outer most query. The solution to overcome this weakness is to either abandon this method or save inner query result to temporary table.

A New Sorting Mechanism in Database

If one wants to add a field to record sorting order, following mechanism is recommended.

In HTML’s Select List or Option List, the positioning indication can be written as:

Before Item 1 – Order_Value is 0
Before Item 2 – Order_Value is 1

Then, the Sort_Order can be written into Database as Order_Value*2-1, which can be applied in both cases as new record or move existing record. By doing this, one can easily insert or move item. This is because in database this field is recorded as 0, 2, 4, 6... Of course, inserting or moving can be done once a time, followed by sorting.

Following is the example in PHP.

public function saveCateNew($order)
{ $orderNew = $order*2-1;
$query = sprintf( ' INSERT INTO CATES_SELF '
. ' (TITLE_1 '
. ' , SORT_ORDER '
. ' ) VALUES '
. ' ("%s" '
. ' , %d '
. ' ) '
, mysql_real_escape_string($this->title_1, $GLOBALS['DB'])
, $orderNew
);
mysql_query($query, $GLOBALS['DB']) or die("An error has ocured: ".mysql_error().":".mysql_errno());
$this->sortCate();
}

public function saveCateOld($order)
{ $orderNew = $order*2-1;
$query = sprintf( ' UPDATE CATES_SELF '
. ' SET TITLE_1 = "%s" '
. ' , SORT_ORDER = %d '
. ' WHERE ID_CATESELF = %d '
, mysql_real_escape_string($this->title_1, $GLOBALS['DB'])
, $orderNew
, $this->id_cateself
);
mysql_query($query, $GLOBALS['DB']) or die("An error has ocured: ".mysql_error().":".mysql_errno());
$this->sortCate();
}

function sortCate()
{ $query = sprintf( ' SELECT ID_CATESELF '
. ' FROM CATES_SELF '
. ' ORDER BY SORT_ORDER '
);
$result = mysql_query($query, $GLOBALS['DB']);
$index = 0;
while($row = mysql_fetch_assoc($result))
{ $id = $row['ID_CATESELF'];
$queryInner = sprintf( ' UPDATE CATES_SELF '
. ' SET SORT_ORDER = %d '
. ' WHERE ID_CATESELF = %d '
, $index
, $id
);
mysql_query($queryInner, $GLOBALS['DB']) or die("An error has ocured: ".mysql_error().":".mysql_errno());
$index = $index+2;
};
mysql_free_result($result);
}