How To Get Rows For N Distinct Values Of A Column

I need to get rows for n distinct invoice numbers. I have SELECT query that gives me results from the table: SELECT A.INVOICE_NUMBER, A.INVOICE_SEQ_NUMBER, B.FILE_NUMBER FROM TABL

Solution 1:

Have you heard of JOIN?

In any case, one way to solve your problem uses DENSE_RANK():

SELECT INVOICE_NUMBER, INVOICE_SEQ_NUMBER, FILE_NUMBER
FROM (SELECT A.INVOICE_NUMBER, A.INVOICE_SEQ_NUMBER, B.FILE_NUMBER,
             DENSE_RANK() OVER (ORDERBY 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;