SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: SQL question

  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL question

    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

  2. #2
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok think I have got it (using info from this recent thread)

    Code:
    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. #3
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry to keep holding this monologue. Another way to do it seems

    Code:
    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    you're over-complicating it...
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this gives only the last orders for each productid, right?
    I want all order records but with an additional lastprice column

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    oh, sorry
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •