In TSQL, How do I create a column in my view so that it display a number on each row that gets incremented?

Thank you.

Naturally you can specify [LAST NAME], [FIRST NAME] or other specific column(s) in the ORDER BY if you want that order for the line#s.

If you want the "order the rows were inserted", there's no way to guarantee that unless you have a datetime in the table that reflects that, and you can sort on that column.

(Sorry, I was busy earlier and didn't have time then to add this extra description.)

Actually, your solution worked as a query but not as a VIEW, which is what I need.  Do you know how to make it work in as a VIEW?

It should work.  Please post the view code so I can see what's wrong.

It's the first field named Line_No, which is blank instead of each line getting an incremented number 1, 2, 3, etc.

VIEW:

SELECT     '' AS Line_No, '' AS REJECTED, DOCNUM AS [External Claim ID], RXCLAIMNUM, MEMBER_ID, DOF, RX_NUMBER, QTY, DS, NCPDP, 
                      RX_NETWORK_ID AS [RX Network ID], PRICING_TERM_DESC AS [Pricing Term Used for Claim], '' AS [Pharmacy AWP Discount Rate], 
                      '' AS [Pharmacy Dispensing Fee], TOTAL_TAXED_LOE_AMOUNT AS [Total Taxed/LOE Amount], UNIT_PRICE_OF_AWP AS [Unit Price of AWP], '' AS [Unit Price of MAC], 
                      '' AS [Ingredient Cost at AWP Discount], '' AS [Ingredient Cost at MAC], '' AS [Claim adjudicate using Lesser of logic], CLAIM_DISCOUNT_RATE AS [Claim Discount Rate],
                       CLAIM_DISPENSING_FEE AS [Claim Dispensing Fee], '' AS [Did Claim Adjudicate with Corect Discount/Disp Fee], '' AS [Additional Pricing review], 
                      FORMULARY_COMPLIANCE_CODE AS [Formulary Compliance Code], '' AS [Expected Copay], '' AS [Copay for Claim], '' AS [Copay Comment], 
                      BRAND_GENERIC_TRADEMARK AS [Brand / Generic Description], 
                      CLAIM_OVERRIDE_REASON_CODE1 + ' ' + CLAIM_OVERRIDE_REASON_CODE2 + ' ' + CLAIM_OVERRIDE_REASON_CODE3 + ' ' + CLAIM_OVERRIDE_REASON_CODE4 +
                       ' ' + CLAIM_OVERRIDE_REASON_CODE5 + ' ' + CLAIM_OVERRIDE_REASON_CODE6 AS [Claim Override Reason Code], '' AS [Claim Reject Code], 
                      '' AS [Claim Reject Reason Code]
FROM         dbo.tbl_FEP_PCL

Select allOpen in new window

Hmm, you're selecting '', try using row_number instead:

VIEW:

SELECT    
    ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Line_No,
...

OK, it does work as a view   but I had to click "ignore " about "SQL text cannot be represented in the grid pane and diagram pane. " and "the query cannot be represented graphically in the diagram and criteria Pane".

Thank you very much.

OK, interesting.  I've never used the gui to create a view so I've never seen that issue.