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
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:
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
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
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
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
