Hello,
I have a table storing chronologically data of sales documents. The documents
can be modified and each modified document is again stored with a new id
(version history).
CREATE TABLE myTable (id INT NOT NULL AUTO_INCREMENT, date DATETIME NOT NULL, subNo SMALLINT NOT NULL, poNo INT NOT NULL, PRIMARY KEY (id))
poNo is the purchase number.
Most of the time the documents do refer not to the whole purchase, but to a
subset or partial quantity only, which is identified by the subNo field/column.
So one purchase can have several subsets. When modifying a document, reference
to the corresponding subset is required. There may be several modifications to
one and the same subset. A table might look like this:
INSERT INTO myTable VALUES (0, '2022-11-01 12:43', 1, 800), (0, '2022-11-02 13:00', 1, 800), (0, '2022-11-03 12:43', 2, 800), (0, '2022-11-03 14:00', 1, 923), (0, '2022-11-03 15:00', 2, 800), (0, '2022-11-04 12:43', 1, 800)
<pre>
Id | Date | SubNo | PO# |
----|------------------|-------|-----|
100 | 2022-11-01 12:43 | 1 | 800 |
101 | 2022-11-02 13:00 | 1 | 800 |
102 | 2022-11-03 12:43 | 2 | 800 |
103 | 2022-11-03 14:00 | 1 | 923 |
104 | 2022-11-03 15:00 | 2 | 800 |
105 | 2022-11-04 12:43 | 1 | 800 |
</pre>
Here, purchase 800 has got two subsets, where #1 was modified twice and #2 once.
I am looking for a query supplying for a given purchase for each of its subsets
the latest document. For PO 800 it would look like this:
Id | Datum | SubNo | PO# |
----|------------------|-------|-----|
105 | 2022-11-04 12:43 | 1 | 800 |
104 | 2022-11-03 15:00 | 2 | 800 |
But I haven't found a way to filter the latest documents.
A rough approach is
SELECT id, date, subNo
FROM myTable
WHERE poNo=800
GROUP BY subNo
ORDER BY subNo, date DESC
but DISTINCT and GROUP BY do not guarantee to return the latest document.
Then I tried to create a VIEW first, to be used in a later query.
CREATE VIEW myView AS
SELECT subNo s, (SELECT MAX(date) FROM myTable WHERE poNo=800 AND subNo=s) AS dd
FROM myTable
WHERE poNo=800
GROUP BY s
But although the query is ok, the result differs when used for a VIEW, probably due to VIEW restrictions.
My MySql version is 8.0.22
Any ideas welcome.