how to use Order by enum

Question

how to use Order by enum

Navigation

#1 by (3 votes)
#2 by (2 votes)

2

Hi, I want to know how to use ORDER BY to do the following in my SQL table column category module I have a ENUM with the following 'media','filme','ova' modes I'd like to make an application to sort as follows in the code below to sort the items in order media, ova e filme this in ORDER BY am2.modulo is possible?

    $medias_skin = $MySQLiconn->query("
    SELECT 'am1'.id,'am1'.cat,'am1'.subcat,'am1'.numero,'am1'.episodioTipo,'am1'.fillerType,'am1'.duploType,'am1'.finalType, 'am1'.autor,'am1'.fansub,'am1'.arquivo_nome,'am1'.url,'am1'.url2,'am1'.url3,'am1'.url4,'am1'.url5,'am1'.url6,'am1'.url7,'am1'.url8,'am1'.url13,'am1'.url14,'am1'.url15,'am1'.url16,'am1'.url17,'am1'.url18,'am1'.url19,'am1'.url20,'am1'.url25,'am1'.url26,'am1'.url27,'am1'.url28,'am1'.url29,'am1'.url30,'am1'.url31,'am1'.url32,'am1'.linknome01,'am1'.linknome02,'am1'.linknome03,'am1'.linknome04,'am1'.linknome05,'am1'.linknome06,'am1'.linknome07,'am1'.linknome08,'am1'.linknome13,'am1'.linknome14,'am1'.linknome15,'am1'.linknome16,'am1'.linknome17,'am1'.linknome18,'am1'.linknome19,'am1'.linknome20,'am1'.linknome25,'am1'.linknome26,'am1'.linknome27,'am1'.linknome28,'am1'.linknome29,'am1'.linknome30,'am1'.linknome31,'am1'.linknome32, 'am1'.player,'am3'.'nome' as mediaNome, 'am3'.'animes_categoria_url' as catUrl, 'am3'.'animes_subcategoria_url' as subcatUrl, 'am2'.'modo' as categoriaModo, 'am2'.'modulo' as categoriaModulo, 'am2'.'nome' as catNome 
    FROM 'm_medias' as am1 USE INDEX (medias_indexadas)
    INNER JOIN 'm_categoria' as am2 USE INDEX (categoria_index) ON 'am1'.'cat' = 'am2'.'id' 
    INNER JOIN 'm_subcategoria' as am3 USE INDEX (subcategoria_index) ON 'am1'.'subcat' = 'am3'.'id' AND 'am1'.'cat' = 'am3'.'cat'
    WHERE am1.id IN ($media_id)
    GROUP BY 'am1'.'id','am1'.'subcat'
    ORDER BY am2.modulo 
    ");

php mysqli

asked by anonymous 23.10.2015 / 05:53

2 answers

2

For numeric data , considering that media is 1, ova is 2 and movie is 3:

am2.modulo - > 1 (average)

am2.modulo - > 2 (move)

am2.modulo - > 3 (film)

ORDER BY AS2.modul ASC

For char or varchar data , you should create a table to indicate the order.

Example

Table modulo_position

Columns:

modulo_id
modulo_position

Finally, everything depends on modeling.

I recommend using the second approach, creating a table where you will have the sort references.

23.10.2015 / 06:11

How to pass parameter via button Count Mysql records

score 3 · Accepted Answer

Based on the example documentation :

Values ENUM are sorted based on their index numbers, which depend on the order in which the enumeration members were listed in the column specification. For example, types "B" before "a" for ENUM ('b', 'a'). The empty string types before non-empty strings, and NULL values come before all other enumeration values.

To avoid unexpected results when using the ORDER BY clause in an ENUM column, use one of the following techniques:


Specify the ENUM list alphabetically.



Make sure that the column is sorted in alphabetical order and not by code number ORDER BY CAST (col AS CHAR) or ORDER BY CONCAT (col).

For a query using CAST , it would then:

SELECT enum_col FROM tbl_name ORDER BY CAST(enum_col AS CHAR);

For your case:

ORDER BY CAST(am2.modulo AS CHAR);