I have a data table of invoices. Each invoice has a client code. I want to use the code in a list box in an OpenOffice Base (ooBase) form (i.e. the front-end) to jump to the invoice with the highest number for that code. So if the client's code is "TDR", and TDR has 60 invoices, it chooses the highest invoice number and navigates to that invoice.
MySQL does not have a "record number" function, but I have found a way of simulating this:
SELECT @row := @row + 1 AS `RowNumber`, invoices.*
FROM invoices, (SELECT @row := 0) `DerivedTable`;
Unfortunately this can't be turned into a VIEW because you get Error 1351: "View's SELECT contains a variable or parameter"
It is also possible to list the client code with the highest invoice next to it:
SELECT invoices.ClientCode, Max(invoices.InvoiceNo) AS LastOfInvoiceNo
GROUP BY invoices.ClientCode
ORDER BY invoices.ClientCode;
The question is how to combine these two SQL commands.
The closest I have got is
SELECT RowNumber, ClientCode, Max(InvoiceNo) AS LastOfInvoiceNo
(SELECT @row := @row + 1 AS `RowNumber`, invoices.*
FROM invoices, (SELECT @row := 0) `DerivedTable`) AS Dtable
GROUP BY ClientCode
ORDER BY ClientCode;
... unfortunately this shows, in the RowNumber column, the LOWEST value for RowNumber! I.e. the "absolute"/"row number" value for the FIRST invoice to have been issued for this client.
Being a newb I don't know what's going on, or why this is so, and basically I am out of my depth.
NB for those who are familiar with MS Access, this sort of navigation is simple in it, and very useful. In MySQL-ooBase you can easily arrange this sort of navigation if the number of lines in the list box equals the number of records in the table. But in the case of "jump to the highest invoice for this client code" this will not be so.