Sort Numbers and seasons correctly

Asked

Viewed 362 times

1

Next I need to formulate a SELECT, whose function is to show episodes in order of seasons, example T1E1, T1E2, T2E1 continuously, for the following structure :

  `eid` int(10) NOT NULL,
  `aid` varchar(6) NOT NULL,
  `hits` int(11) NOT NULL,
  `titulo` varchar(200) DEFAULT NULL,
  `numero` varchar(15) DEFAULT NULL,
  `descargas` text,
  `videos` text NOT NULL,
  `estado` int(1) NOT NULL DEFAULT '0',
  `fecha_salida` date NOT NULL DEFAULT '0000-00-00',
  `temporada` varchar(15) DEFAULT NULL

is returning in this way : http://sqlfiddle.com/#! 9/32c11b7/2

mysql

  • temporada stores the season number, or what? , and which field represents the episode?

  • Da to use CONCAT to join words and print with SELECT, but you must use "ORDER BY season,episode" to sort, if they are integer numbers and not words (varchar)

  • In the Case the "number" represents the episode, and "season", and the number of the same.

2 answers

1


The problem is that the columns numero and temporada were declared as VARCHAR, therefore it is being sorted alphabetically. To fix this problem, state it as INTEGER or carry out the ORDER BY together with the CAST as follows:

SELECT *
  FROM `lista_animes_capitulos`
 ORDER BY CAST(`temporada` AS unsigned),
          CAST(`numero` AS unsigned)

See working on SQL Fiddle.

0

Mysql Documentation: ORDER BY Optimization 

SELECT * FROM `tabela` ORDER BY `temporada`, `numero`;

You can also change the order of each field with ASC (Smaller to larger) and DESC (greater for lesser), as the documentation suggests.

  • does not yet work, as the test http://sqlfiddle.com/#! 9/32c11b7/6 returns out of order the numbers.

  • Your table structure is wrong! Change the following: numero int(11) DEFAULT NULL, temporada int(11) DEFAULT NULL http://sqlfiddle.com/#! 9/e0c52/1

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.