Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

sql select statement counting the results

Posted on 2009-04-19
4
Medium Priority
?
280 Views
Last Modified: 2012-05-06
i am using sql server 2000. every time i am executing a select statement (f.e) select fielda, fieldb from table_a results come as follwing
 
a1 a2
b1 b2
c1 c2
d1 d2
..  ...

how can i create a column which will contains a increment by 1 counter. the select in this case should return

1  a1 a2
2  b1 b2
3  c1 c2
4  d1 d2
.......
........
.........
0
Comment
Question by:basilhs_s
  • 2
  • 2
4 Comments
 
LVL 8

Expert Comment

by:SNilsson
ID: 24178536
You should be able to use the ROW_NUMBER function:

http://msdn.microsoft.com/en-us/library/ms186734.aspx
0
 

Author Comment

by:basilhs_s
ID: 24178549
row_number is not supported in sql server 2000
0
 
LVL 8

Accepted Solution

by:
SNilsson earned 2000 total points
ID: 24178559
Yes sorry, I noticed that as soon as I posted it.
You will need to do a workaround using a temp table with an identity column, an example can be found here:
http://www.mutuallybeneficial.com/index_files/RowNumber_2005.htm
0
 

Author Closing Comment

by:basilhs_s
ID: 31571939
perfect answer for two reason. i saw how we count rows in sql server 2000 and since we are qoing to upgrade to sql sever 2008 soon i learned how i can use the row_counter() function there.. thanks very much
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

810 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