{"id":137,"date":"2008-11-16T02:08:21","date_gmt":"2008-11-16T01:08:21","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=137"},"modified":"2008-11-15T13:47:04","modified_gmt":"2008-11-15T12:47:04","slug":"enigme-order-by","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2008\/11\/16\/enigme-order-by\/","title":{"rendered":"\u00c9nigme&#8230; order by"},"content":{"rendered":"<p>Cela part d&rsquo;un probl\u00e8me tout simple,\u00a0 trier les donn\u00e9es d&rsquo;une table:<\/p>\n<pre>mysql&gt; SELECT id, technologie FROM enigme;<\/pre>\n<pre>+------+-------------+<\/pre>\n<pre>| id\u00a0\u00a0 | technologie |<\/pre>\n<pre>+------+-------------+<\/pre>\n<pre>|\u00a0\u00a0\u00a0 1 | PHP\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n<pre>|\u00a0\u00a0\u00a0 2 | LINUX\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n<pre>|\u00a0\u00a0\u00a0 3 | MySQL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n<pre>+------+-------------+<\/pre>\n<p>Pour trier les enregistrements en fonction de la technologie, faisons un <strong>ORDER BY<\/strong> sur la colonne technologie:<\/p>\n<pre>mysql&gt; SELECT id, technologie FROM enigme ORDER BY technologie;<\/pre>\n<pre>+------+-------------+<\/pre>\n<pre>| id\u00a0\u00a0 | technologie |<\/pre>\n<pre>+------+-------------+<\/pre>\n<pre>|\u00a0\u00a0\u00a0 1 | PHP\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n<pre>|\u00a0\u00a0\u00a0 2 | LINUX\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n<pre>|\u00a0\u00a0\u00a0 3 | MySQL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n<pre>+------+-------------+<\/pre>\n<p>Il semble que le serveur n&rsquo;a pas compris ce que je lui demande, ou alors je ne maitrise pas du tout l&rsquo;alphabet..<br \/>\nRajoutons la clause <strong>ASC <\/strong>(au cas o\u00f9)<\/p>\n<pre>mysql&gt; SELECT id, technologie FROM enigme ORDER BY technologie ASC;<\/pre>\n<pre>+------+-------------+<\/pre>\n<pre>| id\u00a0\u00a0 | technologie |<\/pre>\n<pre>+------+-------------+<\/pre>\n<pre>|\u00a0\u00a0\u00a0 1 | PHP\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n<pre>|\u00a0\u00a0\u00a0 2 | LINUX\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n<pre>|\u00a0\u00a0\u00a0 3 | MySQL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n<pre>+------+-------------+<\/pre>\n<p>Pas mieux !<br \/>\neh ben mince alors !!!<br \/>\nMySQL ne veut d\u00e9cidement pas trier mes donn\u00e9es comme je le souhaites. Et il devenu fou ? Dois-je changer de SGBDR ?<\/p>\n<p>En fait un simple zoom sur la structure de la table nous permet de voir la chose d&rsquo;un autre oeil, le bon !<\/p>\n<pre>mysql&gt; SHOW CREATE TABLE enigme;<\/pre>\n<pre>*************************** 1. row ************************<\/pre>\n<pre> Table: enigme<\/pre>\n<pre>Create Table: CREATE TABLE `enigme` (<\/pre>\n<pre> `id` int(11) DEFAULT NULL,<\/pre>\n<pre> `technologie` enum('PHP','LINUX','MySQL') DEFAULT NULL<\/pre>\n<pre>) ENGINE=MyISAM DEFAULT CHARSET=latin1<\/pre>\n<p>La colonne technologie est de type <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/enum.html\" target=\"_blank\"><strong>ENUM<\/strong><\/a>&#8230;<\/p>\n<p>Les donn\u00e9es stock\u00e9es disposent d&rsquo;un index num\u00e9rique utilis\u00e9 par MySQL pour les manipuler. C&rsquo;est \u00e9galement cet index qui est utilis\u00e9 pour le tri. En d&rsquo;autres termes, PHP vaut 1, LINUX vaut 2 et MySQL vaut 3, par cons\u00e9quent le tri fonctionne correctement.<\/p>\n<h4><span style=\"text-decoration: underline;\">Alors comment faire pour avoir le r\u00e9sultat escompt\u00e9<\/span> ?<\/h4>\n<p>Il faut forcer MySQL \u00e0 utiliser la valeur chaine de caract\u00e8res et non l&rsquo;index:<\/p>\n<ul>\n<li>\n<h5>la fonction <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/string-functions.html#function_concat\" target=\"_blank\">concat<\/a>()<\/h5>\n<\/li>\n<\/ul>\n<pre>mysql&gt; SELECT id, technologie FROM enigme ORDER BY concat(technologie);<\/pre>\n<pre>+------+-------------+<\/pre>\n<pre>| id\u00a0\u00a0 | technologie |<\/pre>\n<pre>+------+-------------+<\/pre>\n<pre>|\u00a0\u00a0\u00a0 2 | LINUX\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n<pre>|\u00a0\u00a0\u00a0 3 | MySQL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n<pre>|\u00a0\u00a0\u00a0 1 | PHP\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/pre>\n<pre>+------+-------------+<\/pre>\n<ul>\n<li>\n<h5>la fonction <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/charset-convert.html\" target=\"_blank\">cast<\/a>()<\/h5>\n<\/li>\n<\/ul>\n<pre>mysql&gt; SELECT id, technologie FROM enigme ORDER BY cast(technologie as char);\r\n+------+-------------+\r\n| id\u00a0\u00a0 | technologie |\r\n+------+-------------+\r\n|\u00a0\u00a0\u00a0 2 | LINUX\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0\u00a0\u00a0 3 | MySQL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0\u00a0\u00a0 1 | PHP\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+------+-------------+<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Cela part d&rsquo;un probl\u00e8me tout simple,  trier les donn\u00e9es d&rsquo;une table:<\/p>\n<p>mysql> SELECT id, technologie FROM enigme;<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"categories":[8],"tags":[47,46,45,44,241,43],"class_list":["post-137","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-cast","tag-concat","tag-enigme","tag-enum","tag-mysql","tag-order-by"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9LfWW-2d","jetpack-related-posts":[{"id":1370,"url":"https:\/\/dasini.net\/blog\/2013\/01\/30\/full-table-scan-vs-full-index-scan-part2-2\/","url_meta":{"origin":137,"position":0},"title":"Full table scan vs Full index scan part2-2","author":"Olivier DASINI","date":"30 janvier 2013","format":false,"excerpt":"2\/ FTS ou FIS Avant de r\u00e9pondre explicitement \u00e0 la question, un petit zoom sur l\u2019une des nombreuses nouveaut\u00e9s de MySQL 5.6. La commande EXPLAIN s\u2019est enrichie de la clause format=json. Elle permet d\u2019avoir une version un peu plus d\u00e9taill\u00e9e que l\u2019EXPLAIN classique. Query 1\/ EXPLAIN format=json SELECT d,avg(price) FROM\u2026","rel":"","context":"Dans &quot;optimisation&quot;","block_context":{"text":"optimisation","link":"https:\/\/dasini.net\/blog\/category\/optimisation\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":56,"url":"https:\/\/dasini.net\/blog\/2008\/11\/02\/auto_increment-differences-myisam-innodb\/","url_meta":{"origin":137,"position":1},"title":"AUTO_INCREMENT: Diff\u00e9rences MyISAM &#8211; InnoDB","author":"Olivier DASINI","date":"2 novembre 2008","format":false,"excerpt":"La clause, AUTO_INCREMENT, permet \u00e0 MySQL de g\u00e9n\u00e9rer un entier unique pour tout nouvel enregistrement d'une table. Cette clause ne peut se mettre que sur les champs de type entier, index\u00e9 et non nul. Elle est donc souvent utilis\u00e9e comme cl\u00e9 primaire. Cependant, sont comportement n'est pas tout \u00e0 fait\u2026","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":779,"url":"https:\/\/dasini.net\/blog\/2009\/10\/01\/utiliser-xml-avec-mysql-5-1-part-55\/","url_meta":{"origin":137,"position":2},"title":"Utiliser XML avec MySQL 5.1 (part 5\/5)","author":"Olivier DASINI","date":"1 octobre 2009","format":false,"excerpt":"Voil\u00e0 un petit panorama de l'utilisation des fonctionnalit\u00e9s XML de MySQL. Comme nous l'avons vu, g\u00e9n\u00e9rer le r\u00e9sultat d'une requ\u00eate au format XML reste tr\u00e8s simple avec le client texte mysql, idem pour la g\u00e9n\u00e9ration d'une sauvegarde (mysqldump). De plus, bien que n'\u00e9tant pas un base de donn\u00e9es XML, ont\u2026","rel":"","context":"Dans &quot;MySQL&quot;","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1102,"url":"https:\/\/dasini.net\/blog\/2011\/08\/29\/duplicate-key-cause-par-un-select\/","url_meta":{"origin":137,"position":3},"title":"Duplicate key caus\u00e9 par un SELECT","author":"Olivier DASINI","date":"29 ao\u00fbt 2011","format":false,"excerpt":"Parmi les erreurs courantes en MySQL, ont trouve la fameuse ERROR 1062 (23000): Duplicate entry '2984' for key 'PRIMARY' caus\u00e9e par la tentative d'insertion d'une donn\u00e9es, d\u00e9j\u00e0 pr\u00e9sente, dans un colonne ayant une contrainte d'unicit\u00e9. Dans la m\u00eame famille il y a aussi l'erreur 1022...","rel":"","context":"Dans &quot;Astuce&quot;","block_context":{"text":"Astuce","link":"https:\/\/dasini.net\/blog\/category\/astuce\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2492,"url":"https:\/\/dasini.net\/blog\/2018\/07\/23\/30-mins-avec-les-fonctions-json-de-mysql\/","url_meta":{"origin":137,"position":4},"title":"30 mins avec les fonctions JSON de MySQL","author":"Olivier DASINI","date":"23 juillet 2018","format":false,"excerpt":"Comme tu le sais,\u00a0JSON (JavaScript Object Notation) est un populaire format d'\u00e9change de donn\u00e9es. Depuis la version 5.7, MySQL supporte un type de donn\u00e9es JSON\u00a0natif (au format interne binaire pour des raisons d'efficacit\u00e9s), ainsi qu'un riche ensemble de fonctions qui te permettront de manipuler dans tout les sens tes documents\u2026","rel":"","context":"Dans &quot;json&quot;","block_context":{"text":"json","link":"https:\/\/dasini.net\/blog\/category\/json-fr\/"},"img":{"alt_text":"MySQL native JSON data type","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/json_icon.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1062,"url":"https:\/\/dasini.net\/blog\/2011\/04\/05\/meet-up-skysql-lemug-fr\/","url_meta":{"origin":137,"position":5},"title":"Meet-up SkySQL &#8211; LeMug.fr","author":"Olivier DASINI","date":"5 avril 2011","format":false,"excerpt":"Le MySQL User Group Francophone (LeMug.fr) et SkySQL Ab vous invitent \u00e0 une rencontre autour des technologies MySQL le mercredi 20 avril 2011. Dans une ambiance d\u00e9contract\u00e9e, ce rassemblement permettra \u00e0 l'ensemble des acteurs de la communaut\u00e9 open source d'\u00e9changer avec l'\u00e9quipe SkySQL Ab (100% MySQLers) pour d\u00e9battre autour de\u2026","rel":"","context":"Dans &quot;MariaDB&quot;","block_context":{"text":"MariaDB","link":"https:\/\/dasini.net\/blog\/category\/mariadb\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/137","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/comments?post=137"}],"version-history":[{"count":7,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/137\/revisions"}],"predecessor-version":[{"id":146,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/137\/revisions\/146"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=137"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=137"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=137"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}