Using rank() in updates - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Using rank() in updates

Author

Topic

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-09-09 : 06:31:01

In my table, I want to set i1 to the row's rank over i2.

create table #table(
i1 int,
i2 int unique
)

insert into #table 
select NULL, 4 union all
select NULL, 7 union all
select NULL, 1

update #table set i1 = rank() over (order by i2)

gives an error: Windowed functions can only appear in the SELECT or ORDER BY clauses. Okay, let's try a subquery then:

update t1 
set i1 = (
	select rank() over (order by i2)
	from #table t2
	where t1.i2 = t2.i2
)
from #table t1

Now i1 equals 1 for all rows.

What I finally came up with is

update t1
set i1 = t2.i1
from #table t1
inner join(
	select i2, rank() over (order by i2) i1
	from #table
) as t2
on t1.i2 = t2.i2

That works, but there is no easier way?

Bjoern

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-09-09 : 06:39:08

I am not sure if there is other better approach. I have done same way couple of times. I am with below option.

update t1
set i1 = t2.i1
from #table t1
inner join(
select i2, rank() over (order by i2) i1
from #table
) as t2
on t1.i2 = t2.i2

Ifor
Aged Yak Warrior

700 Posts

Posted - 2008-09-09 : 07:07:55

This is an alternative:

UPDATE #table
SET i1 =
(
    SELECT i1
    FROM
    (
        SELECT i2, RANK() OVER (ORDER BY i2) AS i1
        FROM #table T
    ) D
    WHERE D.i2 = #table.i2
)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-09 : 07:51:53

Let's investigate!

Sunil (identical solution as OP

)


  |--Table Update(#table)
       |--Table Spool
            |--Compute Scalar
                 |--Top(ROWCOUNT est 0)
                      |--Nested Loops(Inner Join #table)
                           |--Sequence Project(rank)
                           |    |--Segment
                           |         |--Segment
                           |              |--Index Scan(#table)
                           |--Index Seek(#table)

Ifor


  |--Table Update(#table)
       |--Table Spool
            |--Compute Scalar
                 |--Nested Loops(Left Outer Join, #table)
                      |--Top(ROWCOUNT est 0)
                      |    |--Index Scan(#table)
                      |--Sequence Project(rank)
                           |--Segment
                                |--Segment
                                     |--Index Scan(#table)

Peso


  |--Table Update(#Table)
       |--Compute Scalar
            |--Top(ROWCOUNT est 0)
                 |--Sequence Project(rank)
                      |--Segment
                           |--Segment
                                |--Index Scan(#Table)

And here is the suggestion

UPDATE	f
SET	f.i1 = f.Yak
FROM	(
		SELECT	i1,
			RANK() OVER (ORDER BY i2) AS Yak
		FROM	#Table
	) AS f

E 12°55'05.63"
N 56°04'39.26"

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-09-09 : 08:40:27

Nice one! Didn't know you can update derived tables ...

(54224 row(s) affected)

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources