Row_Number() 1

[ReportingAnalyst]

Is Row_Number() a function within SQL Server 2000?

I guess not, because it errs.

My code is:

INSERT INTO chain1(seq, emp, chain)
SELECT row_number() Over (order by chain) as seq,
emp,
chain
from Organization

 

[SQLDenis]

No it's not

however you can do something simmilar see here

http://sqlservercode.blogspot.com/2006/03/ranking-in-sql-server-2000.html

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[ptheriault]

no it isn't. Check out this thread it may help.

thread183-1306393

- Paul
- Database performance looks fine, it must be the Network!

 

[ReportingAnalyst]

In order to apply to this my example, do I need to create another table called #Ranks

--ROW_NUMBER() equivalent in SQL Server 2000

SELECT IDENTITY(INT, 1,1) AS Rank ,Value
INTO #Ranks FROM Rankings WHERE 1=0

INSERT INTO #Ranks
SELECT Value FROM Rankings
ORDER BY Value

SELECT * FROM #Ranks

 

[SQLDenis]

yes you need a temp table with an identity column

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[ReportingAnalyst]

Ok. I created even a Rankings and #Ranks table.

--ROW_NUMBER() equivalent in SQL Server 2000

CREATE TABLE Rankings (Value Char(1))
INSERT INTO Rankings
SELECT 'A' UNION ALL
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'B' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D' UNION ALL
SELECT 'E' UNION ALL
SELECT 'F' UNION ALL
SELECT 'F' UNION ALL
SELECT 'F'

SELECT IDENTITY(INT, 1,1) AS Rank ,Value
INTO #Ranks FROM Rankings WHERE [b]1=0[/b]

INSERT INTO #Ranks
SELECT Value FROM Rankings
ORDER BY Value

SELECT * FROM #Ranks

What does the bold part mean?

Now what ranking do I insert into my statement?

INSERT INTO chain1(seq, emp, chain)
SELECT row_number() Over (order by chain) as seq,
emp,
chain
from Organization

Thanks.

 

[gmmastros]

Are you referring to this...

[tt][blue]SELECT IDENTITY(INT, 1,1) AS Rank ,Value
INTO #Ranks FROM Rankings WHERE 1=0[/blue][/tt]

A select into will cause a table lock for the duration of the select into statement.

By adding Where 1-0, you will effectively create the table structure (for #Rankings) without adding any data. This will prevent a table lock (which is a good thing).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ReportingAnalyst]

So would my SQL 2000 version of my example be like this:

INSERT INTO chain1(seq, emp, chain)
SELECT IDENTITY(INT, 1,1) as seq,
emp,
chain
from Organization

--SELECT IDENTITY(INT, 1,1) AS Rank ,Value
--INTO #Ranks FROM Rankings WHERE 1=0

 

[Catadmin]

ReportingAnalyst & George,

I'm curious. Where did you get the information on "WHERE 1=0" for the Rank thing?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLDenis]

this would be your code

SELECT IDENTITY(INT, 1,1) AS Rank ,seq, emp, chain
INTO #Ranks FROM Organization WHERE 1=0



INSERT INTO #Ranks
SELECT seq, emp, chain
FROM Organization
--ORDER BY <columns here>

SELECT * FROM #Ranks
ORDER BY Rank

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[SQLDenis]

>>I'm curious. Where did you get the information on "WHERE 1=0" for the Rank thing?


Catadmin this has nothing to do with ranking
This technique is used to make a copy of a table quickly without data (or indexes, constraints etc etc)


for example in the pubs database

use pubs
go

select * into OtherAuthors
from authors
where 1 = 0


select * from OtherAuthors

the reason i am creating the table and then inserting instead of 1 step is because the ordering might be wrong if you do it in one shot


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[Catadmin]

Aha... I learn something new every day. @=)

Thanks, Denis.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ReportingAnalyst]

Thanks for the tip. It worked.