SQL question

Databases
1

Let say I have the following table


CREATE TABLE orders (
  orderid INTEGER NOT NULL AUTO_INCREMENT,
  productid INTEGER,
  price DOUBLE,
  PRIMARY KEY (orderid)
)

with the following records


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


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

which returns


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.


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

ok think I have got it (using info from this recent thread)


SELECT
  o.orderid,
  o.productid,
  o.price,
  m.lastprice
FROM
  orders AS o
INNER JOIN
  (SELECT
     n.productid,
     n.price AS lastprice
   FROM orders AS n
   WHERE n.orderid = (SELECT
                        MAX(orderid)
                      FROM
                        orders
                      WHERE productid = n.productid
                      )
  ) AS m
ON
  m.productid = o.productid

I would be interested if there is another or better way to do it

3

sorry to keep holding this monologue. Another way to do it seems


SELECT
  o.orderid,
  o.productid,
  o.price,
  m.price AS lastprice
FROM (
  orders AS o
INNER JOIN
   (SELECT
      productid,
      MAX(orderid) AS maxorderid
    FROM
      orders
    GROUP BY
      productid
   ) AS n
ON
  n.productid = o.productid
)
INNER JOIN
  orders AS m
ON
  m.orderid = n.maxorderid

so wondering if it makes a difference performance wise.

4

you’re over-complicating it…

SELECT o.orderid
     , o.productid
     , o.price AS lastprice
  FROM ( SELECT productid
              , MAX(orderid) AS maxorderid
           FROM orders
         GROUP 
             BY productid ) AS n
INNER 
  JOIN orders AS o
    ON o.productid = n.productid
   AND o.orderid   = n.maxorderid

:slight_smile:

5

this gives only the last orders for each productid, right?
I want all order records but with an additional lastprice column

6

oh, sorry