How to get max sequence number?

Question

This is a continuation of my previous post : here

I have this query:

SELECT INVOICE_NUMBER, INVOICE_SEQ_NUMBER, FILE_NUMBER
FROM (SELECT A.INVOICE_NUMBER, A.INVOICE_SEQ_NUMBER, B.FILE_NUMBER,
             DENSE_RANK() OVER (ORDER BY A.INVOICE_NUMBER) as seqnum
      FROM TABLE1 A JOIN
           TABLE2 B 
           ON A.INVOICE_NUMBER = B.INVOICE_NUMBER AND 
              A.INVOICE_SEQ_NUMBER = B.INVOICE_SEQ_NUMBER
     ) t
WHERE seqnum <= 3;

And this result:

-----------------------------------------------------
| INVOICE_NUMBER | INVOICE_SEQ_NUMBER | FILE_NUMBER |
------------------------------------------------------
|1111111111-1    |          1         | P4324324525 |
-----------------------------------------------------
|1111111111-1    |          2         | P4565674574 |
-----------------------------------------------------
|1111111111-1    |          3         | V4324552557 |
-----------------------------------------------------
|1111111111-1    |          4         | V4324552525 |
-----------------------------------------------------
|2222222222-2    |          1         | S4563636574 |
-----------------------------------------------------
|3333333333-3    |          1         | Q4324325675 |
-----------------------------------------------------
|3333333333-3    |          2         | Q4565674574 |
-----------------------------------------------------

So the new requirement is how do I get the maximum invoice sequence number for the same invoice number? The result should be like this:

------------------------------------------------------------------------
| INVOICE_NUMBER | INVOICE_SEQ_NUMBER | FILE_NUMBER |MAX_INV_SEQ_NUMBER|
------------------------------------------------------------------------
|1111111111-1    |          1         | P4324324525 |        4         |
------------------------------------------------------------------------
|1111111111-1    |          2         | P4565674574 |        4         |
------------------------------------------------------------------------
|1111111111-1    |          3         | V4324552557 |        4         |
------------------------------------------------------------------------
|1111111111-1    |          4         | V4324552525 |        4         |
------------------------------------------------------------------------
|2222222222-2    |          1         | S4563636574 |        1         |
------------------------------------------------------------------------
|3333333333-3    |          1         | Q4324325675 |        2         |
------------------------------------------------------------------------
|3333333333-3    |          2         | Q4565674574 |        2         |
------------------------------------------------------------------------

score 1 · Accepted Answer · answered May 31 '19 at 18:13

SELECT INVOICE_NUMBER, INVOICE_SEQ_NUMBER, FILE_NUMBER, MAX(INVOICE_SEQ_NUMBER) OVER (PARTITION BY INVOICE_NUMBER)
FROM (SELECT A.INVOICE_NUMBER, A.INVOICE_SEQ_NUMBER, B.FILE_NUMBER,
             DENSE_RANK() OVER (ORDER BY A.INVOICE_NUMBER) as seqnum
      FROM TABLE1 A JOIN
           TABLE2 B 
           ON A.INVOICE_NUMBER = B.INVOICE_NUMBER AND 
              A.INVOICE_SEQ_NUMBER = B.INVOICE_SEQ_NUMBER
     ) t
WHERE seqnum <= 3;

Essentially, you just need this in your select statement:

MAX(INVOICE_SEQ_NUMBER) OVER (PARTITION BY INVOICE_NUMBER)

score 1 · Answer 2 · answered May 31 '19 at 18:20

1

Add the following expression to the select list:
, max(INVOICE_SEQ_NUMBER) over (partition by INVOICE_NUMBER) as MAX_INV_SEQ_NUMBER.

answered May 31 '19 at 18:20

Mark Barinstein

11,456
2
8
16

score 0 · Answer 3 · answered May 31 '19 at 18:13

Add an extra column in the selection part as below-

SELECT 
INVOICE_NUMBER, 
INVOICE_SEQ_NUMBER, 
FILE_NUMBER,
(
    SELECT COUNT(*) 
    FROM TABLE1 A 
    JOIN TABLE2 B 
    ON A.INVOICE_NUMBER = B.INVOICE_NUMBER 
    AND A.INVOICE_SEQ_NUMBER = B.INVOICE_SEQ_NUMBER
    AND A.INVOICE_NUMBER = t.INVOICE_NUMBER 
)MAX_INV_SEQ_NUMBER
FROM  ........

How to get max sequence number?

3 Answers3