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

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
paultran00Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
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 ...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
paultran00Author Commented:
Thank you.
0
Scott PletcherSenior DBACommented:
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.)
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

FREE DOWNLOAD
paultran00Author 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?
0
Scott PletcherSenior DBACommented:
It should work.  Please post the view code so I can see what's wrong.
0
paultran00Author 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

0
Scott PletcherSenior DBACommented:
Hmm, you're selecting '', try using row_number instead:

VIEW:

SELECT    
    ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Line_No,
...
0
paultran00Author 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.
0
Scott PletcherSenior DBACommented:
OK, interesting.  I've never used the gui to create a view so I've never seen that issue.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.