PHP/MySQL ->
Query that retrieves last invoice made by sales-person

Suppose that we have to find out the the last invoice made by every sales-person of Colombo-Ltd.

USE colombo;

/*
this select retrives last invoice date for sales-person number 8
*/

SELECT MAX(a.DocDate) FROM Invoice AS a WHERE a.SlpCode = 8;

/*
this select retrives last invoice number for sales-person number 8 in the last date. the point is that sales-person can make several invoices per day
*/

SELECT MAX(b.DocNo)
FROM Invoice AS b
WHERE b.SlpCode = 8 AND b.DocDate =
   (SELECT MAX(a.DocDate) FROM Invoice AS a
    WHERE a.SlpCode = 8);

/*
now we'll add one more level and will ask for the list of all sales-persons instead of specific one. sales-person number is equal to EmployeeId in Employees table
*/

SELECT c.DocNo AS InvoiceNo, c.DocDate, c.CustomerName,
CONCAT( Trim(e.FirstName), ' ', Trim(e.LastName)) AS SalesPerson
FROM Invoice AS c INNER JOIN Employees AS e
ON c.SlpCode = e.EmpNo
WHERE c.DocNo =
(SELECT MAX(b.DocNo)
  FROM Invoice AS b
  WHERE b.SlpCode = c.SlpCode AND b.DocDate =
   (SELECT MAX(a.DocDate) FROM Invoice AS a
    WHERE a.SlpCode = c.SlpCode));


Query results will look like this:
InvoiceNoDocDateCustomerNameSalesPerson
121999-10-15Famke Bacherdona fiber
252000-01-28Anna Matiasleonardo rota
141999-11-11Eva Longoriaaiva pesh
221999-12-02Valentina Mazepagina sohn



sqlexamples.info