title: Last Invoice using Postgres
body: <p>I have a Postgres 9.1 database with three tables - Customer, Invoice, and Line_Items</p>
  
  <p>I want to create a customer list showing the customer and last invoice date for any customer with a specific item (specifically all invoices that have the line_items.code beginning with 'L3').</p>
  
  <p>First, I am trying to pull the one transaction for each customer (the last invoice with the 'L3" code)  (figuring I can JOIN the customer names once this list is created).</p>
  
  <p>Tables are something like this:</p>
  
  <p>Customers</p>
  
  <pre><code>cust_number   last_name   first_name
  ===========   ========   ====================
  1             Smith      John
  2             Jones      Paul
  3             Jackson    Mary
  4             Brown      Phil
  </code></pre>
  
  <p>Transactions</p>
  
  <pre><code>trans_number   date            cust_number
  ===========   ===========      ====================
  1001          2014-01-01        1
  1002          2014-02-01        4
  1003          2014-03-02        2
  1004          2014-03-06        3
  </code></pre>
  
  <p>Line_Items</p>
  
  <pre><code>trans_number   date            item_code
  ===========   ===========      ====================
  1001          2014-01-01        L3000
  1001          2014-01-01        M2420
  1001          2014-01-01        L3500
  1002          2014-02-01        M2420
  1003          2014-03-02        M2420
  1004          2014-03-06        L3000
  </code></pre>
  
  <p>So far, I have:</p>
  
  <pre><code>Select transactions.cust_number, transactions.trans_number
  from transactions 
  where transactions.trans_number in 
      ( SELECT  Line_Items.trans_number
          FROM Line_Items
          WHERE Line_Items.item_code ilike 'L3%'
          ORDER BY line_items.date DESC
      )
  order by transactions.pt_number
  </code></pre>
  
  <p>This pulls all the invoices for each customer with an 'L3' code on the invoice, but I can't figure out how to just have the last invoice.</p>