CODEWITHSUNDEEP
sqlteradata
khedidja tihar
khedidja tihar

Reputation: 25

Get Min date as condition

I have a table that contains invoices for all phone numbers, and each number has several invoices, I want to display only the first invoice for precise number but i don't really know how get only first invoice , this is my query

SELECT
     b.contrno
     a.AR_INVDATE
     FROM P_STG_TABS.IVM_INVOICE_RECORD a 

     INNER JOIN P_EDW_TMP.invoice b
     ON b.contrno=a.contrno

     WHERE a.AR_INVDATE< (SELECT AR_INVDATE FROM P_STG_TABS.IVM_INVOICE_RECORD WHERE contrno=b.contrno  )

Upvotes: 0

Views: 392

Answers (3)

dnoeth
dnoeth

Reputation: 60462

Teradata supports a QUALIFY clause to filter the result of an OLAP-function (similar to HAVING after GROUP BY), which greatly simplifies Tim Biegeleisens's answer:

SELECT *
FROM P_STG_TABS.IVM_INVOICE_RECORD a 
INNER JOIN P_EDW_TMP.invoice b
   ON b.contrno = a.contrno
QUALIFY
   ROW_NUMBER()
   OVER (PARTITION BY b.contrno 
         ORDER BY a.AR_INVDATE)  = 1

Additionally you can apply the ROW_NUMBER before the join (might be more efficient depending on additional conditions):

SELECT *
FROM 
 ( SELECT * 
   FROM P_STG_TABS.IVM_INVOICE_RECORD a 
   QUALIFY
      ROW_NUMBER()
      OVER (PARTITION BY b.contrno 
            ORDER BY a.AR_INVDATE)  = 1
 ) AS a
INNER JOIN P_EDW_TMP.invoice b
   ON b.contrno = a.contrno

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

Use ROW_NUMBER():

SELECT
    t.contrno,
    t.AR_INVDATE
FROM
(
    SELECT
        b.contrno,
        a.AR_INVDATE,
        ROW_NUMBER() OVER (PARTITION BY b.contrno ORDER BY a.AR_INVDATE) rn
    FROM P_STG_TABS.IVM_INVOICE_RECORD a 
    INNER JOIN P_EDW_TMP.invoice b
        ON b.contrno = a.contrno
) t
WHERE t.rn = 1;

If you are worried about ties, and you want to display all ties, then you can replace ROW_NUMBER with either RANK or DENSE_RANK.

Upvotes: 2

Oto Shavadze
Oto Shavadze

Reputation: 42753

If I correctly understand, then one way is to use group by with min(a.AR_INVDATE):

SELECT
b.contrno,
min(a.AR_INVDATE)
FROM P_STG_TABS.IVM_INVOICE_RECORD a 
INNER JOIN P_EDW_TMP.invoice b
ON b.contrno=a.contrno
group by b.contrno

Upvotes: 0

Related Questions