how to do one followed by numbers in mysql

Question

how to do one followed by numbers in mysql

Navigation

#1 by (0 votes)

0

In a database I have the following problem:

ID  premio ordenar precio Fecha       escala_Vencida
1   1      1       1000   30-12-2016  si
2   1      2       2000   30-12-2016  si
3   1      3       3000   30-12-2016  si
4   2      4       4000   30-12-2016  si
5   2      5       5000   30-12-2016  si
6   2      6       6000   30-12-2016  si
7   3      1       6000   30-1-2017   no
8   3      2       4000   30-1-2017   no
9   3      3       5000   30-1-2017   no
10  4      4       1000   30-1-2017   no
11  4      5       2000   30-1-2017   no
12  4      6       3000   30-1-2017   no

I need to make the reward is ordered the problem is the culumna (order) I need to make the number smaller to greater from 1 to infinity as long as the scale is not valid, that is to say it is marked (NO) and the price is lower a major, said in this case the number 6 represent 2 different prizes, in the case of the first prize the order is OK (from ID 1 to 6) but the second is messy (from ID 7 to 12) the second prize I need to insert it in an orderly way using a MySQL insert.

INSERT INTO TABLE
(
premio,
ordenar,
precio,
fecha,
escala_vencida,
)
VALUES
(
premio,
ordenar,<--- problema al ordenar
precio,
fecha,
escala_vencida,
)

IMPORTANT the ID is UNIQUE is not repeated throughout the Table.

Obviously this should do from a SELECT and detail is that I do not know how to order it from what I mentioned above.

mysql mysqli

asked by Juan Carlos Villamizar Alvarez 14.01.2017 в 14:28

source

1 answer

Does not refresh Datepicker when using beforeShowDay Read USB device and send data to another device

score 0 · Answer 1

In other databases there is the OVER clause that determines a partition or range of values, on which to apply a window function, or an aggregate function.

For example: Number the records, creating a partition by scaleVenced, ordered by price. That is, it is numbering as long as it does not change the Vendor scale.

ROW_NUMBER () OVER (PARTITION BY scaleVenced ORDER BY price)

In MySql this function does not exist and must be done using variables. The advantage is that the variables can be initialized in the same SQL statement.

select 
       @ordenar := @ordenar + 1 as ordenar,
       ID, premio, precio, Fecha, escala_vencida
from
    (select @ordenar := 0) i,
    (select ID, premio, precio, Fecha, escala_Vencida 
     from premios 
     where escala_Vencida = 'no' 
     order by precio) t;


+---------+----+--------+--------+---------------------+----------------+
| ordenar | ID | premio | precio |        Fecha        | escala_vencida |
+---------+----+--------+--------+---------------------+----------------+
|    1    | 10 |    4   |  1000  | 30.01.2017 00:00:00 |       no       |
+---------+----+--------+--------+---------------------+----------------+
|    2    | 11 |    4   |  2000  | 30.01.2017 00:00:00 |       no       |
+---------+----+--------+--------+---------------------+----------------+
|    3    | 12 |    4   |  3000  | 30.01.2017 00:00:00 |       no       |
+---------+----+--------+--------+---------------------+----------------+
|    4    |  8 |    3   |  4000  | 30.01.2017 00:00:00 |       no       |
+---------+----+--------+--------+---------------------+----------------+
|    5    |  9 |    3   |  5000  | 30.01.2017 00:00:00 |       no       |
+---------+----+--------+--------+---------------------+----------------+
|    6    |  7 |    3   |  6000  | 30.01.2017 00:00:00 |       no       |
+---------+----+--------+--------+---------------------+----------------+

You can try here: link