November 14, 2007 at 1:40 am
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.
November 14, 2007 at 2:47 am
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
November 19, 2007 at 3:49 am
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
November 19, 2007 at 3:56 am
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
November 19, 2007 at 3:57 am
Maybe something like this:
select (select count(*) from sysobjects c where c.id < a.id) + 1
from sysobjects a
order by id
lp, Matjaz
November 19, 2007 at 4:37 am
You can get the row numbers in sql server 2005 using this
ROW_NUMBER ( ) OVER ( [ )
November 19, 2007 at 4:46 am
Hi ,
I'm using sql server 2000. ...
Please, concentrate on posts.
November 19, 2007 at 5:21 am
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
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply