none
ROW_NUMBER() OVER(PARTITION BY RRS feed

  • Question

  • Hi

    I Hope that someone can help with the following:

    My code is as follows:

    SELECT     TOP (100) PERCENT INVOICE_LINE_TYPE, TRANSACTION_TYPE_NAME, INVOICE_NUMBER, INVOICE_DATE, PAYMENT_TERM, [Order] AS NOMINAL_CODE, ROW_NUMBER() OVER(PARTITION BY INVOICE_NUMBER ORDER By [ORDER]) AS INVOICE_LINE_NUMBER

    FROM         dbo.[1_Oracle_Sub_Table]

    WHERE     (INVOICE_NUMBER = N'701806')

    ORDER BY INVOICE_NUMBER, NOMINAL_CODE, INVOICE_LINE_TYPE

    and I get the following results:

    INVOICE_LINE_TYPE TRANSACTION_TYPE_NAME INVOICE_NUMBER INVOICE_DATEPAYMENT_TERM NOMINAL_CODE INVOICE_LINE_NUMBER

    LINE Invoice – Corp 701806 01/05/2019 NET 14 41001      2

    TAX Invoice – Corp 701806 01/05/2019 NET 14 41001      1

    LINE Invoice – Corp 701806 01/05/2019 NET 14 41024      3

    TAX Invoice – Corp 701806 01/05/2019 NET 14 41024      4

    LINE Invoice – Corp 701806 01/05/2019 NET 14 53500      6

    TAX Invoice – Corp 701806 01/05/2019 NET 14 53500      5

    But what I would like to achieve is a grouping  on the invoice line number(Last Column):

    INVOICE_LINE_TYPE TRANSACTION_TYPE_NAME INVOICE_NUMBER INVOICE_DATEPAYMENT_TERM NOMINAL_CODE INVOICE_LINE_NUMBER

    LINE Invoice – Corp 701806 01/05/2019 NET 14 41001      1

    TAX Invoice – Corp 701806 01/05/2019 NET 14 41001      1

    LINE Invoice – Corp 701806 01/05/2019 NET 14 41024      2

    TAX Invoice – Corp 701806 01/05/2019 NET 14 41024      2

    LINE Invoice – Corp 701806 01/05/2019 NET 14 53500      3

    TAX Invoice – Corp 701806 01/05/2019 NET 14 53500      3

    Please accept my thanks in anticipation

    Kind regards

    Colin

    Tuesday, May 7, 2019 9:40 AM

Answers

  • Hi

    I Hope that someone can help with the following:

    My code is as follows:

    SELECT     TOP (100) PERCENT INVOICE_LINE_TYPE, TRANSACTION_TYPE_NAME, INVOICE_NUMBER, INVOICE_DATE, PAYMENT_TERM, [Order] AS NOMINAL_CODE, ROW_NUMBER() OVER(PARTITION BY INVOICE_NUMBER ORDER By [ORDER]) AS INVOICE_LINE_NUMBER

    FROM         dbo.[1_Oracle_Sub_Table]

    WHERE     (INVOICE_NUMBER = N'701806')

    ORDER BY INVOICE_NUMBER, NOMINAL_CODE, INVOICE_LINE_TYPE

    and I get the following results:

    INVOICE_LINE_TYPE TRANSACTION_TYPE_NAME INVOICE_NUMBER INVOICE_DATEPAYMENT_TERM NOMINAL_CODE INVOICE_LINE_NUMBER

    LINE Invoice – Corp 701806 01/05/2019 NET 14 41001      2

    TAX Invoice – Corp 701806 01/05/2019 NET 14 41001      1

    LINE Invoice – Corp 701806 01/05/2019 NET 14 41024      3

    TAX Invoice – Corp 701806 01/05/2019 NET 14 41024      4

    LINE Invoice – Corp 701806 01/05/2019 NET 14 53500      6

    TAX Invoice – Corp 701806 01/05/2019 NET 14 53500      5

    But what I would like to achieve is a grouping  on the invoice line number(Last Column):

    INVOICE_LINE_TYPE TRANSACTION_TYPE_NAME INVOICE_NUMBER INVOICE_DATEPAYMENT_TERM NOMINAL_CODE INVOICE_LINE_NUMBER

    LINE Invoice – Corp 701806 01/05/2019 NET 14 41001      1

    TAX Invoice – Corp 701806 01/05/2019 NET 14 41001      1

    LINE Invoice – Corp 701806 01/05/2019 NET 14 41024      2

    TAX Invoice – Corp 701806 01/05/2019 NET 14 41024      2

    LINE Invoice – Corp 701806 01/05/2019 NET 14 53500      3

    TAX Invoice – Corp 701806 01/05/2019 NET 14 53500      3

    Please accept my thanks in anticipation

    Kind regards

    Colin

    looks like this to me

    SELECT     TOP (100) PERCENT INVOICE_LINE_TYPE, TRANSACTION_TYPE_NAME, INVOICE_NUMBER, INVOICE_DATE, PAYMENT_TERM, [Order] AS NOMINAL_CODE, DENSE_RANK() OVER(PARTITION BY [INVOICE_NUMBER] ORDER BY [Order]) AS INVOICE_LINE_NUMBER
    FROM 
    dbo.[1_Oracle_Sub_Table]
    WHERE     (INVOICE_NUMBER = N'701806')
    
    ORDER BY INVOICE_NUMBER, NOMINAL_CODE, INVOICE_LINE_TYPE
    



    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by ColinJ Tuesday, May 7, 2019 10:17 AM
    Tuesday, May 7, 2019 9:55 AM
    Moderator

All replies

  • It should be (I don't have SSMS at the moment):

    SELECT     TOP (100) PERCENT INVOICE_LINE_TYPE, TRANSACTION_TYPE_NAME, INVOICE_NUMBER, INVOICE_DATE, PAYMENT_TERM, [Order] AS NOMINAL_CODE, ROW_NUMBER() OVER(PARTITION BY [ORDER] ORDER By [ORDER]) AS INVOICE_LINE_NUMBER

    Please mark as answer if this post helped you


    • Edited by DIEGOCTN Tuesday, May 7, 2019 9:51 AM
    Tuesday, May 7, 2019 9:51 AM
  • Hi,

    Use Dense_rank instead :

    DENSE_RANK() OVER (ORDER BY INVOICE_NUMBER)


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Tuesday, May 7, 2019 9:53 AM
    Answerer
  • Hi

    I Hope that someone can help with the following:

    My code is as follows:

    SELECT     TOP (100) PERCENT INVOICE_LINE_TYPE, TRANSACTION_TYPE_NAME, INVOICE_NUMBER, INVOICE_DATE, PAYMENT_TERM, [Order] AS NOMINAL_CODE, ROW_NUMBER() OVER(PARTITION BY INVOICE_NUMBER ORDER By [ORDER]) AS INVOICE_LINE_NUMBER

    FROM         dbo.[1_Oracle_Sub_Table]

    WHERE     (INVOICE_NUMBER = N'701806')

    ORDER BY INVOICE_NUMBER, NOMINAL_CODE, INVOICE_LINE_TYPE

    and I get the following results:

    INVOICE_LINE_TYPE TRANSACTION_TYPE_NAME INVOICE_NUMBER INVOICE_DATEPAYMENT_TERM NOMINAL_CODE INVOICE_LINE_NUMBER

    LINE Invoice – Corp 701806 01/05/2019 NET 14 41001      2

    TAX Invoice – Corp 701806 01/05/2019 NET 14 41001      1

    LINE Invoice – Corp 701806 01/05/2019 NET 14 41024      3

    TAX Invoice – Corp 701806 01/05/2019 NET 14 41024      4

    LINE Invoice – Corp 701806 01/05/2019 NET 14 53500      6

    TAX Invoice – Corp 701806 01/05/2019 NET 14 53500      5

    But what I would like to achieve is a grouping  on the invoice line number(Last Column):

    INVOICE_LINE_TYPE TRANSACTION_TYPE_NAME INVOICE_NUMBER INVOICE_DATEPAYMENT_TERM NOMINAL_CODE INVOICE_LINE_NUMBER

    LINE Invoice – Corp 701806 01/05/2019 NET 14 41001      1

    TAX Invoice – Corp 701806 01/05/2019 NET 14 41001      1

    LINE Invoice – Corp 701806 01/05/2019 NET 14 41024      2

    TAX Invoice – Corp 701806 01/05/2019 NET 14 41024      2

    LINE Invoice – Corp 701806 01/05/2019 NET 14 53500      3

    TAX Invoice – Corp 701806 01/05/2019 NET 14 53500      3

    Please accept my thanks in anticipation

    Kind regards

    Colin

    looks like this to me

    SELECT     TOP (100) PERCENT INVOICE_LINE_TYPE, TRANSACTION_TYPE_NAME, INVOICE_NUMBER, INVOICE_DATE, PAYMENT_TERM, [Order] AS NOMINAL_CODE, DENSE_RANK() OVER(PARTITION BY [INVOICE_NUMBER] ORDER BY [Order]) AS INVOICE_LINE_NUMBER
    FROM 
    dbo.[1_Oracle_Sub_Table]
    WHERE     (INVOICE_NUMBER = N'701806')
    
    ORDER BY INVOICE_NUMBER, NOMINAL_CODE, INVOICE_LINE_TYPE
    



    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by ColinJ Tuesday, May 7, 2019 10:17 AM
    Tuesday, May 7, 2019 9:55 AM
    Moderator
  • Hi Many thanks for the quick response 

    That gives the following results

    INVOICE_LINE_TYPE TRANSACTION_TYPE_NAME INVOICE_NUMBER INVOICE_DATE PAYMENT_TERM NOMINAL_CODE INVOICE_LINE_NUMBER
    TAX Invoice – Corp 701806 01/05/2019 NET 14 41001 1
    LINE Invoice – Corp 701806 01/05/2019 NET 14 41001 2
    LINE Invoice – Corp 701806 01/05/2019 NET 14 41024 1
    TAX Invoice – Corp 701806 01/05/2019 NET 14 41024 2
    TAX Invoice – Corp 701806 01/05/2019 NET 14 53500 1
    LINE Invoice – Corp 701806 01/05/2019 NET 14 53500 2

    Where as Iam looking for

    INVOICE_LINE_TYPE TRANSACTION_TYPE_NAME INVOICE_NUMBER INVOICE_DATE PAYMENT_TERM NOMINAL_CODE INVOICE_LINE_NUMBER
    TAX Invoice – Corp 701806 01/05/2019 NET 14 41001 1
    LINE Invoice – Corp 701806 01/05/2019 NET 14 41001 1
    LINE Invoice – Corp 701806 01/05/2019 NET 14 41024 2
    TAX Invoice – Corp 701806 01/05/2019 NET 14 41024 2
    TAX Invoice – Corp 701806 01/05/2019 NET 14 53500 3
    LINE Invoice – Corp 701806 01/05/2019 NET 14 53500 3

    IE keeping the 'Line' and associated 'TAX' elements together.Apologies it wont retain the format

    ie

    Nominal 1 'Line' Invoice Line number 1

    Nominal 1 'Tax' Invoice Line number 1

    Nominal 2 'Line' Invoice Line number 2

    Nominal 2 'Tax' Invoice Line Number 2

    Etc.

    kind regards

    Colin




    • Edited by ColinJ Tuesday, May 7, 2019 10:15 AM
    Tuesday, May 7, 2019 10:05 AM
  • Many Thanks - Much appreciated

    Works a treat

    Kind regards

    Colin

    Tuesday, May 7, 2019 10:18 AM