How can I sort this table mysql

Question

How can I sort this table mysql

Navigation

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

3

I have the following table where I sort by prioridad and id_agenda , the order should be as follows Priority ASC and id_agenda DESC (In case a data does not have priority so it must go below the if it has 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 is 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 sort by ASC priority, the table gets messed up leaving the last id first. I would like to have a result like that.

---------------------------
|id |prioridad|  actividad|
----|---------|-----------|
|51 |   1     |act1       | <--Ordena por primera prioridad
|60 |   2     |act2       |
|62 |   3     |act3       |
|71 |         |act5       | <--Ultimo valor ingresado
|70 |         |act4       |
|---|---------|-----------|

mysql query

asked by MoteCL 26.10.2018 в 14:33

source

2 answers

4

When ordering by the field prioridad in order ASC , the ones in null are placed first. A variant to achieve this is to make a union of two queries, first with those that are not in null and placing the others behind.

(SELECT * FROM ACT_Agenda 
where prioridad is not null ORDER BY prioridad ASC, id_agenda DESC )
union
(select * from ACT_Agenda where prioridad is null order by id_agenda DESC LIMIT 1000)

Note that the parentheses in the second part of the query are important because they do not apply the last order by to the result of the union, returning to the original situation.

answered by 26.10.2018 в 15:32

Hide and show div effect What are the models for Laravel 5.3 for?

score 3 · Accepted Answer

Effectively in MySQL, NULLs are considered less than any other non-NULL value, except if you add the character - before the name of the column in the Order By and the ASC is changed to DESC or vice versa. Look at the following link: Comparison of different SQL implementations

SqlFiddle: sqlfiddle-example

The query should look like this:

SELECT * FROM ACT_Agenda as a
       ORDER BY -prioridad DESC, id_agenda DESC

And you will have the expected result.