Last Invoice using Postgres

Question

I have a Postgres 9.1 database with three tables - Customer, Invoice, and Line_Items

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').

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).

Tables are something like this:

Customers

cust_number   last_name   first_name
===========   ========   ====================
1             Smith      John
2             Jones      Paul
3             Jackson    Mary
4             Brown      Phil

Transactions

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

Line_Items

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

So far, I have:

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

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.

score 3 · Answer 1 · edited May 23 '17 at 12:13

3

Use DISTINCT ON:

SELECT DISTINCT ON (t.cust_number)
       t.cust_number, t.trans_number
FROM   line_items l
JOIN   transactions t USING (trans_number)
WHERE  l.item_code ILIKE 'L3%'
ORDER  BY t.cust_number, l.date DESC;

This returns at most one row per cust_number - the one with the latest trans_number. You can add more columns to the SELECT list freely.

Detailed explanation:

Select first row in each GROUP BY group?

edited May 23 '17 at 12:13

Community

1
1

answered Nov 05 '14 at 18:15

Erwin Brandstetter

605,456
145
1,078
1,228

This works amazingly well. I was not aware of the difference between SELECT DISTINCT and SELECT DISTINCT ON (). I appreciate your help. Thank you. – kipsoft Nov 05 '14 at 21:53

Dominik P · Answer 2 · 2014-11-05T18:22:09.503

0

you could use MIN or MAX:

SELECT Line_Items.trans_number, Max(line_items.date) As [last]
From Line_Items
Group By Line_Items.trans_number

edited Nov 05 '14 at 18:22

answered Nov 05 '14 at 18:11

Dominik P

117
10

Last Invoice using Postgres

2 Answers2