Know the latest price of Mysql products

Question

Know the latest price of Mysql products

Navigation

#1 by (1 votes)

1

I have a table of products where their characteristics are and another table "db_stock" where the activities of the products are managed. These activities may vary the purchase price or sale price of the products, even several times a day.

I have searched Stackoverflow (For example get the latest insert entered sql ) and other pages but what comes closest is to use the top 1, order by ... limit 1, having max (date), select max (date) but I do not know if I have used them badly but I can not build the sentence.

I have created tables and views to see the last record done, and then join the registration id and extract the prices but I do something wrong and it returns me other dates ... even dates of the type 0000-00-00 00:00 : 00 ... (That last using the select max or having max date.

Here some examples failed: (I use the product 3115 as a unit test since a product can have many activity records and price changes) There are approximately 19,000 products and many more records in stock

    create view ultimosPrecios as 
select idproductos, cproveedor, csalida 
from db_stock
where max(registro) and cproveedor != "" and csalida != "";

create view ultimosPrecios5 as 
select idproducto, cproveedor, csalida,  max(registro)
from db_stock
where  cproveedor != "" and csalida != "" and cproveedor != 0 and csalida != 0
group by idproducto;

select count(a.idproducto),a.idproducto from (
SELECT distinct idproducto
FROM db_stock)a
GROUP BY idproducto;

create view ultimoReg as 
SELECT distinct idproducto, max(registro)
FROM db_stock
GROUP BY idproducto;

SELECT DISTINCT id, idproducto, csalida, cproveedor, MAX( registro ) 
FROM db_stock
WHERE cproveedor !=  ""
AND csalida !=  ""
AND cproveedor !=0
AND csalida !=0
AND idproducto =  '3115'
GROUP BY idproducto
LIMIT 0 , 30

SELECT DISTINCT id, idproducto, b.cproveedor, MAX( a.registro )
FROM db_stock a inner join (select cproveedor, registro
from db_stock )b 
ON a.registro = b.registro 
WHERE a.idproducto =  '3115'
GROUP BY a.idproducto
LIMIT 0 , 300

create view vista_preciosPro as
SELECT ur.id, ur.idproducto, sp.csalida, sp.cproveedor 
FROM db_stock sp inner join ultimoReg ur ON sp.id = ur.id;

Guys, I still can not solve my problem and I've been with this for 4 days now. It seems super simple, however, there is no solution. I asked for authorization to eliminate and work with only data that have correct records and within 2017, and even then I do not give with the solution.

mysql

asked by Mauro Alejandro Perez Araya 04.04.2017 в 14:57

source

1 answer

Is it possible to use a value of javascripts in razor asp.net? Modify javascript object

score 1 · Answer 1

I interpret that what you want to do is show the last price of the table "db_stock" if it is so, the table I imagine that it has an "id" autonumérico. If so, the only thing you should do is in the where of each query put a IN of "id" to the same query, but return "max (id)". try that way, then I'll give you the example:

create view ultimosPrecios as 
select idproductos, cproveedor, csalida 
from db_stock
where cproveedor != "" and csalida != ""
and id in (select max(id) from db_stock
where cproveedor != "" and csalida != "");

On the other hand, this query should return the last record inserted for a given product.

This will work as long as the last price is on it.

create view ultimosPrecios as 
select a.idproductos, a.cproveedor, a.csalida 
from db_stock a
where a.cproveedor != "" and a.csalida != ""
and a.id in (select max(b.id) from db_stock b
where a.idproductos = b.idproductos);