Write a SQL query to return rows corresponding to the most recent

Transact-SQL
1

Considering the database schema displayed in the SQLServer-style diagram below, write a SQL query to return only the most recent invoice for each customer. For each customer, show the Customer ID, Customer Name, Invoice ID, and the BillingDate. If the customer has no invoices, display 0 for the Invoice ID and 1-1-1900 for the BillingDate.
Also write a second version of the query to display only the customer’s 2nd invoice.

invoices(id, Billingdate, customerid) , Customers(id, Name, ReferredBy)

2 
DECLARE @Customers table(id int, CustName varchar(100), ReferredBy varchar(100));
INSERT @Customers ( id, CustName, ReferredBy )
VALUES ( 0, 'Bob', 'Unknown' )
     , ( 1, 'Ted', 'Bob' )
     , ( 2, 'Carol', 'Ted' )
     , ( 3, 'Alice', 'Carol' )
     , ( 4, 'Jeb', 'George' )
     , ( 5, 'Eddie', 'Ella' );
DECLARE @Invoices table(id int IDENTITY, Billingdate date, customerid int);
INSERT @Invoices ( Billingdate, customerid )
VALUES ( '20180117', 5 )
     , ( '20180122', 2 )
     , ( '20180227', 4 )
     , ( '20180228', 1 )
     , ( '20180314', 0 )
     , ( '20180422', 3 )
     , ( '20180518', 2 )
     , ( '20180312', 5 )
     , ( '20180213', 4 )
     , ( '20180104', 0 )
     , ( '20180311', 1 )
     , ( '20180601', 2 )
     , ( '20180617', 4 )
     , ( '20180119', 3 )
     , ( '20180508', 1 )
     , ( '20180425', 2 )
     , ( '20180418', 5 );
WITH MostRecent AS
(SELECT c.CustName
     , c.ReferredBy
     , i.id InvoiceNum
     , i.BillingDate
     , Row_Number() OVER (ORDER BY i.Billingdate DESC) rn     
FROM @Customers c
JOIN @Invoices i ON c.id = i.customerid)
SELECT
   MostRecent.CustName
 , MostRecent.ReferredBy
 , MostRecent.InvoiceNum
 , MostRecent.Billingdate
FROM MostRecent
WHERE MostRecent.rn = 1;
WITH SecondMostRecent AS
(SELECT c.CustName
     , c.ReferredBy
     , i.id InvoiceNum
     , i.BillingDate
     , Row_Number() OVER (ORDER BY i.Billingdate DESC) rn     
FROM @Customers c
JOIN @Invoices i ON c.id = i.customerid)
SELECT
   SecondMostRecent.CustName
 , SecondMostRecent.ReferredBy
 , SecondMostRecent.InvoiceNum
 , SecondMostRecent.Billingdate
FROM SecondMostRecent
WHERE SecondMostRecent.rn = 2;

image