Re: Need to find last price and date product was sold
Please, try to do the follow select, i think it´ll works fine. select product_code, max(date_sold), price_sold from trans group by product_code order by product_code mos [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] This should be easy but I can't find a way of doing it in 1 step. I have a Trans table like: Product_Code: X(10) Date_Sold: Date Price_Sold: Float Now there will be 1 row for each Product_Code, Date combination. So over the past year a product_code could have over 300 rows, one row for each day it was sold. There are thousands of products. What I need to do is find the last price_sold for each product_code. Not all products are sold each day so a product might not have been sold for weeks. The only solution I've found is to do: drop table if exists CurrentPrices; create table CurrentPrices select Prod_Code, cast(max(Date_Sold) as Date), -1.0 Price_Sold from Trans group by Prod_Code; alter table CurrentPrices add index ix_ProdCode (Prod_Code); update CurrentPrices CP, Trans T set CP.Price_Sold=T.Price_Sold and T.Date_Sold=CP.Date_Sold; Is there a way to shorten this? It may take 2-3 minutes to execute. I don't really need a new table as long as I get the Prod_Code and the last Date_Sold. TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need to find last price and date product was sold
Section 3.6.2 of the 4.1 manual has this example using a subselect: SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop); I use this basic syntax with max(date) alot. - Original Message - From: João Cândido de Souza Neto [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, September 28, 2006 8:39 AM Subject: Re: Need to find last price and date product was sold Please, try to do the follow select, i think it´ll works fine. select product_code, max(date_sold), price_sold from trans group by product_code order by product_code mos [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] This should be easy but I can't find a way of doing it in 1 step. I have a Trans table like: Product_Code: X(10) Date_Sold: Date Price_Sold: Float Now there will be 1 row for each Product_Code, Date combination. So over the past year a product_code could have over 300 rows, one row for each day it was sold. There are thousands of products. What I need to do is find the last price_sold for each product_code. Not all products are sold each day so a product might not have been sold for weeks. The only solution I've found is to do: drop table if exists CurrentPrices; create table CurrentPrices select Prod_Code, cast(max(Date_Sold) as Date), -1.0 Price_Sold from Trans group by Prod_Code; alter table CurrentPrices add index ix_ProdCode (Prod_Code); update CurrentPrices CP, Trans T set CP.Price_Sold=T.Price_Sold and T.Date_Sold=CP.Date_Sold; Is there a way to shorten this? It may take 2-3 minutes to execute. I don't really need a new table as long as I get the Prod_Code and the last Date_Sold. TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need to find last price and date product was sold
Mike, What I need to do is find the last price_sold for each product_code. SELECT t1.product_code,t1.date_sold,t1.price_sold FROM trans AS t1 LEFT JOIN trans AS t2 ON t1.product_code = t2.product_code AND t1.price_sold t2.price_sold WHERE t2.product_code IS NULL ORDER BY t1.product_code; There's a bit of discussion at http://www.artfulsoftware.com/queries.php#7/ PB - mos wrote: This should be easy but I can't find a way of doing it in 1 step. I have a Trans table like: Product_Code: X(10) Date_Sold: Date Price_Sold: Float Now there will be 1 row for each Product_Code, Date combination. So over the past year a product_code could have over 300 rows, one row for each day it was sold. There are thousands of products. What I need to do is find the last price_sold for each product_code. Not all products are sold each day so a product might not have been sold for weeks. The only solution I've found is to do: drop table if exists CurrentPrices; create table CurrentPrices select Prod_Code, cast(max(Date_Sold) as Date), -1.0 Price_Sold from Trans group by Prod_Code; alter table CurrentPrices add index ix_ProdCode (Prod_Code); update CurrentPrices CP, Trans T set CP.Price_Sold=T.Price_Sold and T.Date_Sold=CP.Date_Sold; Is there a way to shorten this? It may take 2-3 minutes to execute. I don't really need a new table as long as I get the Prod_Code and the last Date_Sold. TIA Mike -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.407 / Virus Database: 268.12.9/458 - Release Date: 9/27/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need to find last price and date product was sold
Neat-o. I think that's much better than the query I proposed with the subselect. However, it doesn't give you price from the last sale of the product, instead it gives you highest price the product was sold for. Also, it can give you multiple rows for each product_code if there are multiple sales at the same price. Here is a small modification to Peter's query which will give you exactly one row for each product code showing the price at the last sale of that product. (Assuming you have a synthetic key, perhaps an autoincrement field, called id) Also, an index on the product_code field will help the speed of this query a lot. (I don't understand why the subselect query is still faster - I don't think it should be.) SQL is rather fun. SELECT t1.product_code,t1.date_sold,t1.price_sold FROM trans AS t1 LEFT JOIN trans AS t2 ON t1.product_code = t2.product_code AND (t1.date_sold t2.date_sold OR (t1.date_sold=t2.date_sold AND t1.idt2.id) WHERE t2.product_code IS NULL ORDER BY t1.product_code; Douglas Sims [EMAIL PROTECTED] On Sep 28, 2006, at 10:12 AM, Peter Brawley wrote: Mike, What I need to do is find the last price_sold for each product_code. SELECT t1.product_code,t1.date_sold,t1.price_sold FROM trans AS t1 LEFT JOIN trans AS t2 ON t1.product_code = t2.product_code AND t1.price_sold t2.price_sold WHERE t2.product_code IS NULL ORDER BY t1.product_code; There's a bit of discussion at http://www.artfulsoftware.com/ queries.php#7/ PB - mos wrote: This should be easy but I can't find a way of doing it in 1 step. I have a Trans table like: Product_Code: X(10) Date_Sold: Date Price_Sold: Float Now there will be 1 row for each Product_Code, Date combination. So over the past year a product_code could have over 300 rows, one row for each day it was sold. There are thousands of products. What I need to do is find the last price_sold for each product_code. Not all products are sold each day so a product might not have been sold for weeks. The only solution I've found is to do: drop table if exists CurrentPrices; create table CurrentPrices select Prod_Code, cast(max(Date_Sold) as Date), -1.0 Price_Sold from Trans group by Prod_Code; alter table CurrentPrices add index ix_ProdCode (Prod_Code); update CurrentPrices CP, Trans T set CP.Price_Sold=T.Price_Sold and T.Date_Sold=CP.Date_Sold; Is there a way to shorten this? It may take 2-3 minutes to execute. I don't really need a new table as long as I get the Prod_Code and the last Date_Sold. TIA Mike -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.407 / Virus Database: 268.12.9/458 - Release Date: 9/27/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need to find last price and date product was sold
At 07:39 AM 9/28/2006, you wrote: Please, try to do the follow select, i think it´ll works fine. select product_code, max(date_sold), price_sold from trans group by product_code order by product_code Unfortunately that doesn't guarantee that the price_sold will match the row with the max(date_sold). Someone gave me the solution via email using a subselect that works well. It goes something like this: select t1a.account, maxdate, amount from (select account, max (date_xact) maxdate from transactions t1 group by account) t1a left join transactions t2 on t1a.account=t2.account and maxdate=t2.date_xact order by t1a.account; Mike mos [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] This should be easy but I can't find a way of doing it in 1 step. I have a Trans table like: Product_Code: X(10) Date_Sold: Date Price_Sold: Float Now there will be 1 row for each Product_Code, Date combination. So over the past year a product_code could have over 300 rows, one row for each day it was sold. There are thousands of products. What I need to do is find the last price_sold for each product_code. Not all products are sold each day so a product might not have been sold for weeks. The only solution I've found is to do: drop table if exists CurrentPrices; create table CurrentPrices select Prod_Code, cast(max(Date_Sold) as Date), -1.0 Price_Sold from Trans group by Prod_Code; alter table CurrentPrices add index ix_ProdCode (Prod_Code); update CurrentPrices CP, Trans T set CP.Price_Sold=T.Price_Sold and T.Date_Sold=CP.Date_Sold; Is there a way to shorten this? It may take 2-3 minutes to execute. I don't really need a new table as long as I get the Prod_Code and the last Date_Sold. TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need to find last price and date product was sold
You could do something like that by using a derived table (subselect) to pick the max date for each product sale and then joining that on the products table again to pick up the amounts. Note that you must use mysql 5.x to be able to use subselects. Also this will return multiple rows for a given account if there are more than one sale on the last day each item has been sold. (There are several ways around that if it's a problem). I just ran such a query on the table I'm working on right now to test the idea... there are about 3000 rows in this table so the 0.52 second query time seems high, but there's only a primary key index. I think indexing on the transaction date (date_xact) would probably help in this example... mysql select t1a.account, maxdate, amount from (select account, max (date_xact) maxdate from transactions t1 group by account) t1a left join transactions t2 on t1a.account=t2.account and maxdate=t2.date_xact order by t1a.account; +-++---+ | account | maxdate| amount| +-++---+ | 0 | 2005-08-17 | -15.06| | 1 | 2006-07-24 | 26790.00 | | 2 | 2006-07-14 | 1500.00 | | 2 | 2006-07-14 | 2040.00 | | 2 | 2006-07-14 | 2520.00 | | 3 | 2006-07-14 | -193.98 | | 3 | 2006-07-14 | -328.98 | | 3 | 2006-07-14 | -418.21 | | 4 | 2006-07-14 | -186.00 | | 4 | 2006-07-14 | -252.96 | | 4 | 2006-07-14 | -312.48 | | 5 | 2006-07-14 | -43.50| | 5 | 2006-07-14 | -59.16| | 5 | 2006-07-14 | -73.08| | 9 | 2006-06-27 | 60.06 | | 9 | 2006-06-27 | 196.77| | 10 | 2006-03-27 | 60.04 | | 11 | 2006-04-13 | 65.00 | | 12 | 2006-06-23 | -272.03 | | 13 | 2006-02-16 | 100.00| | 14 | 2006-07-14 | 114.75| | 14 | 2006-07-14 | 156.06| | 14 | 2006-07-14 | 192.78| | 15 | 2006-07-24 | -18240.00 | | 15 | 2006-07-24 | -8550.00 | | 16 | 2006-07-11 | -800.00 | | 17 | 2004-07-07 | -51.87| | 17 | 2004-07-07 | -50.49| | 17 | 2004-07-07 | -27.31| | 18 | 2006-06-01 | 288.77| | 19 | 2006-05-11 | 175.00| | 20 | 2006-01-05 | 50.00 | | 21 | 2006-07-15 | 152.90| | 22 | 2006-07-19 | -600.00 | | 23 | 2006-05-31 | 10.00 | | 24 | 2005-07-29 | -277.83 | | 25 | 2005-11-08 | -178.00 | | 26 | 2006-03-24 | 94.24 | | 26 | 2006-03-24 | 74.40 | | 26 | 2006-03-24 | 248.00| | 27 | 2006-03-24 | 22.04 | | 27 | 2006-03-24 | 17.40 | | 27 | 2006-03-24 | 58.00 | | 28 | 2006-06-07 | 185.00| | 29 | 2006-03-27 | 136.00| | 30 | 2006-07-18 | 398.16| | 31 | 2006-02-04 | 500.00| | 32 | 2006-04-06 | 64.00 | | 35 | 2006-04-15 | 1000.00 | | 37 | 2005-12-23 | 200.00| | 38 | 2006-05-12 | -51.04| | 39 | 2005-07-31 | 1191.00 | | 40 | 2006-05-29 | 65.00 | +-++---+ 53 rows in set (0.52 sec) Douglas Sims [EMAIL PROTECTED] On Sep 27, 2006, at 11:36 PM, mos wrote: This should be easy but I can't find a way of doing it in 1 step. I have a Trans table like: Product_Code: X(10) Date_Sold: Date Price_Sold: Float Now there will be 1 row for each Product_Code, Date combination. So over the past year a product_code could have over 300 rows, one row for each day it was sold. There are thousands of products. What I need to do is find the last price_sold for each product_code. Not all products are sold each day so a product might not have been sold for weeks. The only solution I've found is to do: drop table if exists CurrentPrices; create table CurrentPrices select Prod_Code, cast(max(Date_Sold) as Date), -1.0 Price_Sold from Trans group by Prod_Code; alter table CurrentPrices add index ix_ProdCode (Prod_Code); update CurrentPrices CP, Trans T set CP.Price_Sold=T.Price_Sold and T.Date_Sold=CP.Date_Sold; Is there a way to shorten this? It may take 2-3 minutes to execute. I don't really need a new table as long as I get the Prod_Code and the last Date_Sold. TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
