• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 464
  • Last Modified:

Make row counter in query

Hello!

How can i do row counter in query like that.

select id from table1
and output will be:

counter  id
  1       4
  2       9
  3       20
  4       21
  5       50
 ........

thank you!
0
kaspiton
Asked:
kaspiton
  • 4
  • 2
  • 2
  • +3
1 Solution
 
george74Commented:
hi,

there is one way you could do this, but it requires you to deal with another table. The following example creates your table one (that you already have), than selects it's values into a new temporary table together with an identity column (used for rowcount).
Hope this helps.

Cheers,
George

set nocount on
create table table1(id int)
declare @i int
set @i = 10
while @i > 0 begin
     insert into table1 select @i
     set @i  = @i - 1
end

select identity(int, 1, 1) as rix, * into #table2 from table1         -- this is the line you need for having the incremented values
select * from #table2

drop table table1
drop table #table2
0
 
nigelrivettCommented:
select counter = (count(*) from table1 t1 where t1.id < table1.id) + 1 ,
id
from table1
order by id
0
 
kaspitonAuthor Commented:
Can i do this without sub queries?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Éric MoreauSenior .Net ConsultantCommented:
I found an article that may interest you: "INF: How to Dynamically Number Rows in a Select Statement"
at http://support.microsoft.com/support/kb/articles/q186/1/33.asp 
0
 
nigelrivettCommented:
Don't see how.
You need to get the data to display from the table and also the number of records in front of it - don't see how you can get that without another copy of the table.

Can join the table to intself but that's not much different from the subquery.
0
 
ibroCommented:
The method nigel suggests will be very slow for a big queries. By big I mean queries that involve tables with large number of records. However it will be suitable for small result-sets. If you involve tables with a large number of records, better use george74's method with temporary table. It will give you best performance.
0
 
Éric MoreauSenior .Net ConsultantCommented:
Please maintain:

Questions Asked 24
Last 10 Grades Given A A A A A A A A A A  
Question Grading Record 20 Answers Graded / 20 Answers Received
0
 
Éric MoreauSenior .Net ConsultantCommented:
I am sure that my solution is the best.
0
 
ibroCommented:
that was very modest :-)
0
 
Éric MoreauSenior .Net ConsultantCommented:
kaspiton, can you comment or close the question ?
0
 
DafvidCommented:
The "INF: How to Dynamically Number Rows in a Select Statement" KB entry link is broken. This one is correct http://support.microsoft.com/default.aspx?scid=kb;en-us;Q186133 for future reference.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now