autonumbering in a select statment

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

autonumbering in a select statment

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-09-28 : 02:35:25

i have a select the gives me some rows from joining some table

how can i in this seelct add a column whihch will give me like an autoincement column (the rows will numbered)

thnaks in advance

peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-28 : 02:39:44

Either use a subquery or use RANK or ROW_NUMBER functions.

Peter Larsson
Helsingborg, Sweden

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-09-28 : 03:00:59

ok wil ill check them outthnaks slot

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-28 : 03:03:22

RANK and ROW_NUMBER functions are used with SQL Server 2005.

Subquery:

select mt1.*,
(select count(*) from mytable mt2 where mt2.somecol <= mt1.othercol)
from mytable mt1

As we don't know the table layout, it is hard to tell which approach that are right for you.

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-09-28 : 03:41:36

Of you could pre-select into a temporary table with an IDENTITY column (presumably using a suitable ORDER BY clause), and then use the Temporary Table & Identity Column as appropriate - e.g. in a JOIN / sub-query:


SELECT	[T_ID] = IDENTITY(int, 1, 1),
	MyPKColumn
INTO    #TEMP
FROM    MyTable
ORDER BY MySequenceColumn

SELECT [T_ID],
       mt1.*,
FROM   MyTable AS MT1
       JOIN #TEMP AS T
           ON T.MyPKColumn = MT1.MyPKColumn

(In the Real World you should pre-create/declare the #TEMP / @TEMP table rather than using SELECT ... INTO)

Kristen

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-09-28 : 06:05:23

quote:

In the Real World you should pre-create/declare the #TEMP / @TEMP table rather than using SELECT ... INTO

why does the first is better then the second??
is it generally less good to use select... into ?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-28 : 06:13:15

It's because of locking overhead involved with SELECT...INTO.

Take a look at following article:

[url]http://www.sql-server-performance.com/transact_sql_select.asp[/url]

Harsh Athalye
India.
"Nothing is Impossible"

Kristen
Test

22859 Posts

Posted - 2006-09-28 : 06:19:45

"is it generally less good to use select... into ?"

Afraid so!

Its fine for "I need a quick copy of this table in case I goof up" type stuff.

If you put

SELECT Column1, Column2, ...
INTO #TEMP
FROM MyTable

you lock the ability to create tables in TEMPDB for the duration of the select (could be a long time on a big select) - whereas

CREATE #TEMP ...
INSERT INTO #TEMP SELECT ...

releases that lock after the CREATE, which is of course very quick.

If you use SELECT ... INTO #TEMP in a stored procedure the optimiser will not make good choices about use of that table - whereas the CREATE TABLE statement provides the optimiser with all it needs to know about the table.

If you use CREATE TABLE #TEMP you can (and IMO should) provide a PK on it; you can also specify indexes, NOT NULL, Collation, etc. etc. - all of which will help to save you from some future disaster by causing the Sproc to fall-over, rather than just battle-on, when it all goes horribly pear-shaped.

Probably a bunch of other reasons why Create Table first is Good Practice, but those are the ones off the top of my head.

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-28 : 08:07:28

quote:
Originally posted by pelegk2

i have a select the gives me some rows from joining some table

how can i in this seelct add a column whihch will give me like an autoincement column (the rows will numbered)

thnaks in advance

peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Where do you want to show data?
If you use front end application, do numbering there

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources