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

paultran00
paultran00 used Ask the Experts™
on
TSQL: SQL SERVER 2008R2

Hi,

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

For example:

data of students:

LAST NAME, FIRSTNAME

white, snow
charming, prince
mouse, mickey


I want this:

LINE #, LAST NAME, FIRST NAME

1, white, snow
2, charming, prince
3, mouse, mickey
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
No guarantee of line# order, but you can do this:

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS [LINE #], [LAST NAME], [FIRST NAME]
FROM ...

Author

Commented:
Thank you.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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.)
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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?
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

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

Author

Commented:
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

Open in new window

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

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

VIEW:

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

Author

Commented:
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.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial