If you want to implement sorting in SQL that's not either numeric or lexical then you need to create a schema that allows you to add numeric or lexical ordering overtop of your data.
eg: map your file extensions to file types, and assign each type a value to order by.
SQLfiddle of below.
CREATE TABLE files (
name VARCHAR(32),
ext VARCHAR(16),
PRIMARY KEY (name, ext),
INDEX (ext)
);
INSERT INTO files (name, ext) VALUES
('file1', 'jpg'),
('file2', 'avi'),
('file3', 'gif'),
('file4', 'mov'),
('aaaaa', 'txt');
CREATE TABLE filetypes (
typeid INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
type VARCHAR(32),
sortindex INTEGER,
PRIMARY KEY (typeid),
INDEX (sortindex)
);
INSERT INTO filetypes (type, sortindex) VALUES
('video',1), ('image', 0), ('text',99);
CREATE TABLE ext_to_type (
ext VARCHAR(16),
typeid INTEGER UNSIGNED,
PRIMARY KEY (ext),
INDEX (typeid)
);
INSERT INTO ext_to_type (ext, typeid) VALUES
('jpg', 2),
('gif', 2),
('avi', 1),
('mov', 1),
('txt', 3);
Then your query becomes:
SELECT CONCAT(f.name, '.', f.ext) as 'filename'
FROM files f INNER JOIN ext_to_type et
ON f.ext = et.ext
INNER JOIN filetypes t
ON et.typeid = t.typeid
ORDER BY t.sortindex, f.name, f.ext
With results:
file1.jpg
file3.gif
file2.avi
file4.mov
aaaaa.txt
The benefit to an in-database approach being:
- You can change your ordering and add more types in the database via your application as opposed to other methods presented where you would have to go back and edit the query in your source code every time you want to add a new file type or alter the ordering.
- Rather than ordering by a conditional expression this takes advantage of defined indexes.