I have the following table where I order it by prioridad
and id_agenda
, the order must be as follows Priority ASC and id_agenda
DESC (In case a data does not have priority so it must go below those that do have priority).
sql
CREATE TABLE IF NOT EXISTS `ACT_Agenda` (
`id_agenda` int(11) NOT NULL AUTO_INCREMENT,
`prioridad` int(11) DEFAULT NULL,
`actividad` text,
PRIMARY KEY (`id_agenda`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=83 ;
INSERT INTO `ACT_Agenda` (`id_agenda`, `prioridad`, `actividad`) VALUES
(51, 1, 'act1'),
(60, 2, 'act2'),
(62, 3, 'act3'),
(70, NULL, 'act4'),
(71, NULL, 'act5'),
Here my code where I order.
$sql = "SELECT * FROM ACT_Agenda as a
ORDER BY prioridad DESC, id_agenda DESC ";
My result. sqlFiddle
---------------------------
|id |prioridad| actividad|
----|---------|-----------|
|62 | 3 |act1 | <--Debería ordenar por la primera prioridad
|60 | 2 |act2 |
|51 | 1 |act3 |
|71 | |act5 | <--Ultimo valor ingresado
|70 | |act4 |
|---|---------|-----------|
But if I order by ASC priority, the table is disordered, leaving the last id
one first. I would like to have a result like this.
---------------------------
|id |prioridad| actividad|
----|---------|-----------|
|51 | 1 |act1 | <--Ordena por primera prioridad
|60 | 2 |act2 |
|62 | 3 |act3 |
|71 | |act5 | <--Ultimo valor ingresado
|70 | |act4 |
|---|---------|-----------|
When sorting by the field
prioridad
in orderASC
, the ones that are in null are placed first. A variant to achieve this is to make a union of two queries, first with those that are not null and placing the others behind.Note that the parentheses in the second part of the query are important because otherwise it applies the last one
order by
to the result of the union, returning to the original situation.Indeed in MySQL, NULLs are considered less than any other non-NULL value, except if you add the character
-
before the column name in the Order By and the ASC is changed to DESC or vice versa. See the following link: Comparison of different SQL implementationsSqlFiddle: sqlfiddle-example
The query should be like this:
And you will have the expected result.