How can I manage a FIFO-queue in an database with SQL?
Posted
by Jonas
on Stack Overflow
See other posts from Stack Overflow
or by Jonas
Published on 2010-06-09T11:26:22Z
Indexed on
2010/06/09
13:32 UTC
Read the original article
Hit count: 225
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 | <- ?
+-------------+----------+-------+
© Stack Overflow or respective owner