SQL advanced -> Find two latest invoices of the sales person

The mission is to find a pair of latest invoices of each sales person employed in Colombo ltd. All nessesary information is stored in "Invoices" table.

Invoices:
DocNoDocDateDocTotalSlpCode...
114/01/99237511...
222/01/991291.511...
314/02/991020010...
410/03/992508...

We just need to identify two records with the latest DocDate value per sales person represented by SlpCode number.

First query (q_LastInvoice_First) is simple it retrieves latest invoice record using subquery with MAX(DocDate):
SELECT a.SlpCode, a.DocNo, a.DocDate
FROM Invoice AS a INNER JOIN
  (SELECT SlpCode, MAX(DocDate) AS MaxDate
   FROM Invoice GROUP BY SlpCode) AS b
ON (a.DocDate = b.MaxDate) AND (a.SlpCode = b.SlpCode);

It returns following results:
SlpCodeDocNoDocDate
112704/03/00
82902/05/00
93014/06/00
103126/06/00

Next we'll define intermidiate query (q_LastInvoice_Tmp ) that will retrieve all invoices except those picked up by the first query:
SELECT t1.SlpCode, t1.DocNo, t1.DocDate, t2.DocNo
FROM Invoice AS t1 LEFT JOIN
  (SELECT a.SlpCode, a.DocNo, a.DocDate
   FROM Invoice AS a INNER JOIN
   (SELECT SlpCode, MAX(DocDate) AS MaxDate
   FROM Invoice GROUP BY SlpCode) AS b
   ON (a.DocDate = b.MaxDate) AND (a.SlpCode = b.SlpCode)) AS t2
ON t1.DocNo = t2.DocNo
WHERE t2.DocNo IS NULL;

After doing this it's easy to define last query ( q_LastInvoice_Second) that will retrieve invoice that is one before the latest:
SELECT a.SlpCode, a.DocNo, a.DocDate
FROM Invoice AS a INNER JOIN
  (SELECT SlpCode, MAX(DocDate) AS MaxDate
  FROM q_LastInvoice_Tmp GROUP BY SlpCode) AS b
ON (a.SlpCode = b.SlpCode) AND (a.DocDate = b.MaxDate);

Finaly we can execute two queries together using UNION:
SELECT a.SlpCode, a.DocNo, a.DocDate
FROM
(SELECT SlpCode, DocNo, DocDate, 1 AS ord_no
  FROM q_LastInvoice_First
   UNION ALL
  SELECT SlpCode, DocNo, DocDate, 2 AS ord_no
  FROM q_LastInvoice_Second) AS a
ORDER BY a.SlpCode, a.ord_no;

And recive:
SlpCodeDocNoDocDate
82902/05/00
81215/10/99
93014/06/00
92608/02/00
103126/06/00
102822/04/00
112704/03/00
112202/12/99



sqlexamples.info