Let say I have the following table

Code:
CREATE TABLE orders (
  orderid INTEGER NOT NULL AUTO_INCREMENT,
  productid INTEGER,
  price DOUBLE,
  PRIMARY KEY (orderid)
)
with the following records

Code:
orderid | productid | price
---------------------------
      1 |         3 |    10
      2 |         1 |     5
      3 |         1 |     7
      4 |         3 |     9
If I want to retrieve per product the price corresponding to the last order I can do the following

Code:
SELECT 
  productid, 
  price AS lastprice
FROM 
  orders AS o 
WHERE 
  orderid = (SELECT 
               MAX(orderid) 
             FROM 
               orders 
             WHERE 
               productid=o.productid
             )
which returns

Code:
productid | lastprice
---------------------
        1 |         7
        3 |         9
But my question is: how do I get all orders with this lastprice as one of the columns? I.e.

Code:
orderid | productid | price | lastprice
---------------------------------------
      1 |         3 |    10 |         9
      2 |         1 |     5 |         7
      3 |         1 |     7 |         7
      4 |         3 |     9 |         9