My SQL change order in order by

Question

In a simple file management script, I have a files table, that if I sort files by extension, it looks like!:

| name | extension^|
+------+-----------+
| bear |    MP4    |
| bird |    PNG    |
| frog |    JPG    |
| lion |    AVI    |

So there is a video at top, then two images and then another video.

How can I use order by to sort files by type (all images then all videos etc)?

I can have another table for types definition, or a JSON file:

{"image" : ["JPG", "PNG", "GIF"],
 "video" : ["MP4", "AVI", "FLV"] }

Answer 1

I would suggest using mimetype instead of extension, so you could easily sort by mimetype ascending or descending.

| name | extension^| mimetype
+------+-----------+-----------
| frog |    JPG    | image/jpeg
| bird |    PNG    | image/png
| lion |    AVI    | video/avi
| bear |    MP4    | video/mp4

Answer 2

You can have junction table to match the extension type

Create table extension
(  type  varchar(20),
   extension varchar(20)
 );

INSERT INTO extension 
values ( 'image', 'JPG'),
 ( 'image', 'PNG'),
 ( 'video', 'MP4'),
 ( 'video', 'AVI')


SELECT F.name, F.extension from files F
JOIN extension E
ON F.extension = E.extension
ORDER BY E.type

Answer 3

Add a third column, like this :

| name | extension^| file_type|
+------+-----------+---------+
| bird |    PNG    |       Img|
| lion |    AVI    |       Vid|
| frog |    JPG    |       Img|
| bear |    MP4    |       Vid|

Then :

SELECT *
FROM files
ORDER BY file_type

Answer 4

Can you add a column to your files table? If so, call it type_id, make it of type tinyint (1). Then insert values to that column depending on value in extension column. Like so:

# UPDATE 'files' SET 'type_id'="1" WHERE 'extension'="MP4" OR 'extension'="AVI";
# UPDATE 'files' SET 'type_id'="2" WHERE 'extension'="PNG" OR 'extension'="JPG";

This way type 1 will mean video and type 2 will mean image.

Then do the same for each content type.

Then order your select query by type_id.

Answer 5

Without changing your schema, you would need to, in essence, hard code your sorting logic into the query by doing something like:

SELECT
  name,
  extension,
  (CASE extension
    WHEN 'GIF' THEN 1
    WHEN 'JPG' THEN 2
    WHEN 'PNG' THEN 3
    WHEN 'AVI' THEN 4
    WHEN 'FLV' THEN 5
    WHEN 'MP4' THEN 6
    ELSE 7
  END) AS sort_value
FROM table
ORDER BY sort_value ASC

Answer 6

You can use a "Case" Statement to add the information you obtain from the JSON Array.

In the example given it would be:

SELECT 
  name,
  ext,
  CASE ext 
    WHEN "MP4" THEN "movie"
    WHEN "AVI" THEN "movie"
    WHEN "FLV" THEN "movie"
    WHEN "JPG" THEN "picture"
    WHEN "PNG" THEN "picture"
    WHEN "GIF" THEN "picture"
    ELSE "unknown"
  END
  AS type
FROM test
ORDER BY type DESC,ext;

See fiddle: http://sqlfiddle.com/#!2/69d243/9

Answer 7

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:

1. 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.
2. Rather than ordering by a conditional expression this takes advantage of defined indexes.

Answer 8

What you need is a third column or another table that you can set an order by for the extension types.

| name | extension^| rank    |
+------+-----------+---------+
| bird |    PNG    |        1|
| lion |    AVI    |        2|
| frog |    JPG    |        1|
| bear |    MP4    |        2|

Personally I would prefer another table but that might make it a bit more complex if your not familiar with joins etc.

The reason I would prefer a table, is that it is more "future proof" meaning that a table like this

| extension^| rank   |
+------------+-------+
|    PNG    |       1|
|    AVI    |       2|
|    JPG    |       1|
|    MP4    |       2|

Will be much easier latter to find what the value of a image file is, because you can query it for your extension types instead of manually entering them in when you add data to the database.

So say they come up with a mp5 a year from now, you would have to in the "Same table" case make code changes, with 2 tables you would only need to insert the new extension, and maybe tweak what file types you allow for the upload ( which you should do in either case anyway ).