CODEWITHSUNDEEP
mysqlsql
Ross
Ross

Reputation: 47057

Selecting most recently added row

I have 3 entities (Orders, Items and OrderItems) with the following schema:

                    OrderItems
    Orders      +---------------+
+-----------+   | id (PK)       |     Items
| id (PK)   |==<| order_id (FK) |   +-------+
| createdAt |   | item_id (FK)  |>==| id    |
+-----------+   | createdAt     |   | name  |
                | quantity      |   +-------+
                +---------------+

I need to keep a history of OrderItems, so that if an OrderItem's quantity is changed we have a record of the original quantity for each successive change.

My problem is that I'd like to be able to select only the most recent items from the table for each order. For example:

First two (initial) OrderItems:
    (id: 1, order_id: 1, item_id: 1, createdAt: 2013-01-12, quantity: 10),
    (id: 2, order_id: 1, item_id: 2, createdAt: 2013-01-12, quantity: 10),

Later order items are amended to have different quantities, creating a new row:
    (id: 3, order_id: 1, item_id: 1, createdAt: 2013-01-14, quantity: 5),
    (id: 4, order_id: 1, item_id: 2, createdAt: 2013-01-14, quantity: 15),

My stab at the query to do this:

SELECT oi.* FROM OrderItems oi
WHERE oi.order_id = 1
GROUP BY oi.item_id
ORDER BY oi.createdAt DESC;

Which I'd hoped would produce this:

| id | order_id | item_id | createdAt  | quantity |
+----+----------+---------+------------+----------+
| 3  | 1        | 1       | 2013-01-14 | 5        |
| 4  | 2        | 2       | 2013-01-14 | 15       |

Actually produced this:

| id | order_id | item_id | createdAt  | quantity |
+----+----------+---------+------------+----------+
| 1  | 1        | 1       | 2013-01-12 | 10       |
| 2  | 2        | 2       | 2013-01-12 | 10       |

At the moment I think that just using the createdAt timestamp should be enough to identify the history of items, however I may move to linking to the previous item from each order item (linked list). If that makes it easier to make this query I'll move to that.

Upvotes: 2

Views: 1022

Answers (2)

bonCodigo
bonCodigo

Reputation: 14361

Here is another solution : definitely not opposing to Mahmoud :D (thanks for the sqlfiddle) If you want to try out.

Query:

SELECT * FROM orderitems
GROUP BY id
ORDER BY createdAt DESC
LIMIT 2
;

Results:

| ID | ORDER_ID | ITEM_ID |                      CREATEDAT | QUANTITY |
-----------------------------------------------------------------------
|  3 |        1 |       1 | January, 14 2013 02:00:00+0000 |        5 |
|  4 |        1 |       2 | January, 14 2013 02:00:00+0000 |       15 |

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

Try this instead:

SELECT 
  oi.*
FROM OrderItems oi
INNER JOIN
(
   SELECT item_id, MAX(createdAt) MaxDate
   FROM OrderItems
   WHERE order_id = 1
   GROUP BY item_id
) o2  ON oi.item_id = o2.item_id
     AND DATE(oi.CreatedAt) = DATE(o2.MaxDate)
ORDER BY oi.createdAt DESC;

SQL Fiddle Demo

This will give you:

| ID | ORDER_ID | ITEM_ID |  CREATEDAT | QUANTITY |
---------------------------------------------------
|  3 |        1 |       1 | 2013-01-14 |        5 |
|  4 |        1 |       2 | 2013-01-14 |       15 |

Upvotes: 3

Related Questions