CODEWITHSUNDEEP
sqlmaxuniquefirebirdgreatest-n-per-group
Simon King
Simon King

Reputation: 195

FirebirdSQL Unique & Max (or MaxValue)

I want to run a report from sales of any customer that has ordered in the last two years.

I can run a report of all invoices dated within two years then remove duplicates in excel, but I would rather do it directly within (Firebird) SQL

I can use a WHERE date < 1 Jan 2015 (2 years or thereabours), but how do I get it to only show the customer once? I thought if I used MAX(Date) therefore showing the most recent date in that two year period. Where am I going wrong? I believe I need to use a UNIQUE() function like UNIQUE(ORDERCUSTOMER) within the SELECT clause.

SELECT
FINANCIALSALESINVOICES.TRANSACTIONDATE,
FINANCIALSALESINVOICES.INVOICECUSTOMER, 
FINANCIALSALESINVOICES.ORDERCUSTOMER,
FINANCIALSALESINVOICES.INVOICENUMBER,
FINANCIALSALESINVOICES.SOURCENUMBER,
MAX(FINANCIALSALESINVOICES.TRANSACTIONDATE)

FROM FINANCIALSALESINVOICES 
WHERE (FINANCIALSALESINVOICES.TRANSACTIONDATE>={d '2015-01-01'})
ORDER BY FINANCIALSALESINVOICES.INVOICECUSTOMER, FINANCIALSALESINVOICES.TRANSACTIONDATE

I did having it showing the max date for each instance of invoice in the past two years, but now can't fine that file or replicate it.

Upvotes: 2

Views: 1382

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522234

One approach is to use a subquery in the WHERE clause which checks for the most recent invoice:

SELECT
    t.TRANSACTIONDATE,
    t.INVOICECUSTOMER, 
    t.ORDERCUSTOMER,
    t.INVOICENUMBER,
    t.SOURCENUMBER
FROM FINANCIALSALESINVOICES t
WHERE t.TRANSACTIONDATE >= date '2015-01-01' AND
      t.TRANSACTIONDATE = (SELECT MAX(f.TRANSACTIONDATE)
                           FROM FINANCIALSALESINVOICES f
                           WHERE t.ORDERCUSTOMER = f.ORDERCUSTOMER AND
                                 f.TRANSACTIONDATE >= date '2015-01-01')
ORDER BY t.INVOICECUSTOMER,
         t.TRANSACTIONDATE

Upvotes: 1

Mark Rotteveel
Mark Rotteveel

Reputation: 109136

With Firebird 3 you can use row_number() to assign a unique value to each row within a group (partition), that value can then be filtered on:

select 
    a.TRANSACTIONDATE,
    a.INVOICECUSTOMER, 
    a.ORDERCUSTOMER,
    a.INVOICENUMBER,
    a.SOURCENUMBER
from (
    select
        TRANSACTIONDATE,
        INVOICECUSTOMER, 
        ORDERCUSTOMER,
        INVOICENUMBER,
        SOURCENUMBER,
        row_number() over (partition by INVOICECUSTOMER, order by TRANSACTIONDATE desc) as rownr
    from FINANCIALSALESINVOICES
    where TRANSACTIONDATE >= date '2015-01-01'
) a
where a.rownr = 1
order by a.INVOICECUSTOMER, a.TRANSACTIONDATE

See also Window (Analytical) Functions in the Firebird 3 release notes.

Upvotes: 1

Related Questions