Running Number Generation

Question

Running Number Generation

Ananth-431739

Ten Centuries

Points: 1185
More actions
November 14, 2007 at 1:40 am

#200441

Can we able to create an query which should display the running number's or serial numbers... For example 1,2,3,4....till end of the records. Identity should not be used. Thr' query i need to generate.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

Ananth (11/14/2007)
Can we able to create an query which should display the running number's or serial numbers... For example 1,2,3,4....till end of the records. Identity should not be used. Thr' query i need to generate.

Hi Ananth,

if you cannot use identity with a temptable (like SELECT IDENTITY( INT, 1,1 ) row_number, *

INTO #temptable

FROM mytable) then depending on whether you are on SQL Server 2000 or 2005 you could either use "tricks" or the row_number() or rank() functions. There are some good examples for both 2000 and 2005 on http://support.microsoft.com/default.aspx?scid=KB;EN-US;q186133

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Ananth-431739 Ten Centuries Points: 1185 More actions · Answer 2

Hi ,

I'm using sql server 2000. Do you have any query or site to view the solution. I need to generate the serial number in the query,so that the query should display the serial number dynamically.

for ex:

select * from then it should display as:-

1 column1 column2

2 column1 column2

.

end of record

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 3

Ananth (11/19/2007)
Hi ,
I'm using sql server 2000. Do you have any query or site to view the solution. I need to generate the serial number in the query,so that the query should display the serial number dynamically.
for ex:
select * from then it should display as:-
1 column1 column2
2 column1 column2
.
.
end of record

On 2000 one of the fastest way to do this is to use a temptable like:

select identity(int, 1,1) as seqnr, * into #temptable from mytable

select * from #temptable

Note that this will require some space in the tempdb, depending on the size of your query. If you do not want to use a temptable, AND your results are small then have a look at the link in my previous post for examples.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Matjaz Justin Mr or Mrs. 500 Points: 598 More actions · Answer 4

Maybe something like this:

select (select count(*) from sysobjects c where c.id < a.id) + 1

from sysobjects a

order by id

lp, Matjaz

zafar-458126 SSC-Addicted Points: 443 More actions · Answer 5

You can get the row numbers in sql server 2005 using this

ROW_NUMBER ( ) OVER ( [ )

Matjaz Justin Mr or Mrs. 500 Points: 598 More actions · Answer 6

Hi ,
I'm using sql server 2000. ...

Please, concentrate on posts.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

Matjaz Justin (11/19/2007)
Maybe something like this:
select (select count(*) from sysobjects c where c.id < a.id) + 1
from sysobjects a
order by id
lp, Matjaz

Ummm... be careful... that's a classic "Triangular Join" and it works quite well... on very low rowcounts. at 8k rows, it'll take about 7 seconds to resolve. It get's worse very quickly...

8k rows = 7 seconds

16k rows = 28 seconds

32k rows = 1 minute 59 seconds

64k rows = 7 minutes 36 seconds

128k rows = 30 minutes 44 seconds

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)