?
Solved

Show a counter starting at 1 in an SQL 2005 View

Posted on 2014-10-16
11
Medium Priority
?
170 Views
Last Modified: 2014-10-17
My final line of data in my SQL 2005 view is a line counter. Can I add a line counter in a SQL 2005 view? I am not sure if that can be done or it I need to create a new table each time and import the data.
0
Comment
Question by:allenkent
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 40385460
I don't know what exactly you are doing on your view, you can add another column doesn't matter
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40385461
Hugh betcha.
CREATE TABLE tmp (numbers int) 
GO

INSERT INTO tmp (numbers) 
VALUES (3), (12), (120),(13), (12), (999) ,(42), (549), (11) 
GO

CREATE VIEW myview AS
SELECT ROW_NUMBER() OVER (ORDER BY numbers)  as counter, numbers
FROM tmp
GO

SELECT * FROM myview
GO

Open in new window

0
 

Author Comment

by:allenkent
ID: 40385558
To answer what I have and what I am doing:
I have a query now that has 832 lines. This view is the data I want but a piece of data is a counter. I need this for an extract to import into someone else system. This is a sample of what I have (and need)(the last part the sequence):

454545 5454512 FS454556       09242014        000000000   00000000001R
454545 8946546 FS454556       09252014        000000000   00000000002R
454545 1557885 FR213317       09252014        000000000   00000000003R
454545 2789942 ET548816       09262014        000000000   00000000004R
454545 5499833 LK712455       09272014        000000000   00000000005R

I ran the above script and got. Nice but I need 850 or so lines of data:

counter      numbers
1      3
2      11
3      12
4      12
5      13
6      42
7      120
8      549
9      999
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 66

Expert Comment

by:Jim Horn
ID: 40385567
Show us the query that returns the above set.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40385787
No points please:

Can I add a line counter in a SQL 2005 view?

YES, use ROW_NUMBER() OVER()

ROW_NUMBER() & OVER() have been available since (and including) SQL Server 2005

see:
http://msdn.microsoft.com/en-us/library/ms186734(v=sql.90).aspx
http://msdn.microsoft.com/en-au/library/ms189461(v=sql.90).aspx

nb, row_number returns BIGINT

(that's up to 9,223,372,036,854,775,807 & is way more than 850! )
0
 
LVL 40

Expert Comment

by:lcohan
ID: 40387128
Or by adding a simply count as the row number to show the "relative record number" - something like in pseudo code below:

SELECT id, (SELECT COUNT(*) FROM table_name t2 WHERE t2.id <= t.id) AS rownumber
FROM table_name t
ORDER BY id
0
 

Author Comment

by:allenkent
ID: 40387372
I am getting errors on both samples. Can you help with code specific? Here is my basic query:

SELECT INVOICE_NUMBER,TRANSACTION_DATE
FROM dbo.INVOICE_HISTORY
WHERE     (TRANSACTION_DATE > CONVERT(DATETIME, '2014-10-01 00:00:00', 102))
0
 
LVL 40

Expert Comment

by:lcohan
ID: 40387414
Please try this:

 
SELECT t.INVOICE_NUMBER,t.TRANSACTION_DATE,
             (SELECT COUNT(*) FROM INVOICE_HISTORY t2 with (NOLOCK) WHERE t2.INVOICE_NUMBER <= t.INVOICE_NUMBER) AS rownumber
  FROM dbo.INVOICE_HISTORY t
  WHERE     (TRANSACTION_DATE > CONVERT(DATETIME, '2014-10-01 00:00:00', 102))
 ORDER BY t.INVOICE_NUMBER
0
 

Author Comment

by:allenkent
ID: 40387428
Works nice but it is not starting from 1. The rownumber your are listing the row number of the database. Example:  Invoice 66010 happens to be row number 64810. This is the number displaying. I want to get count from 1 to x on my SQL view.
0
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 40387440
Right - sorry...this should work and add the order by if you need your record set ordered:


SELECT INVOICE_NUMBER,TRANSACTION_DATE,
            ROW_NUMBER() OVER (ORDER BY INVOICE_NUMBER)  as rownumber
 FROM dbo.INVOICE_HISTORY
 WHERE     (TRANSACTION_DATE > CONVERT(DATETIME, '2014-10-01 00:00:00', 102))
0
 

Author Closing Comment

by:allenkent
ID: 40387449
That is beautiful! Perfect.

SELECT INVOICE_NUMBER,TRANSACTION_DATE,
             ROW_NUMBER() OVER (ORDER BY INVOICE_NUMBER)  as rownumber
  FROM dbo.INVOICE_HISTORY
  WHERE     (TRANSACTION_DATE > CONVERT(DATETIME, '2014-10-01 00:00:00', 102))
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question