|
|
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:
| InvoiceNo | DocDate | CustomerName | SalesPerson |
| 12 | 1999-10-15 | Famke Bacher | dona fiber |
| 25 | 2000-01-28 | Anna Matias | leonardo rota |
| 14 | 1999-11-11 | Eva Longoria | aiva pesh |
| 22 | 1999-12-02 | Valentina Mazepa | gina sohn |
|