How can I manage a FIFO-queue in an database with SQL?
- by Jonas
I have two tables in my database, one for In and one for Out. They have two columns, Quantity and Price. How can I write a SQL-query that selects the correct price?
In example: If I have 3 items in for 75 and then 3 items in for 80. Then I have two out for 75, and the third out should be for 75 (X) and the fourth out should be for 80 (Y).
How can I write the price query for X and Y? They should use the price from the third and forth row. In example, is there any way to SELECT the third row in the In-table? I can not use auto_increment as identifier for i.e. "third" row, because the tables will contain post for other items too. The rows will not be deleted, they will be saved for accountability reasons.
SELECT Price FROM In WHERE ...?
NEW database design:
+----+
| In |
+----+------+-------+
| Supply_ID | Price |
+-----------+-------+
| 1 | 75 |
| 1 | 75 |
| 1 | 75 |
| 2 | 80 |
| 2 | 80 |
+-----------+-------+
+-----+
| Out |
+-----+-------+-------+
| Delivery_ID | Price |
+-------------+-------+
| 1 | 75 |
| 1 | 75 |
| 2 | X | <- ?
| 3 | Y | <- ?
+-------------+-------+
OLD database design:
+----+
| In |
+----+------+----------+-------+
| Supply_ID | Quantity | Price |
+-----------+----------+-------+
| 1 | 3 | 75 |
| 2 | 3 | 80 |
+-----------+----------+-------+
+-----+
| Out |
+-----+-------+----------+-------+
| Delivery_ID | Quantity | Price |
+-------------+----------+-------+
| 1 | 2 | 75 |
| 2 | 1 | X | <- ?
| 3 | 1 | Y | <- ?
+-------------+----------+-------+
