Partitioning with Analytics and Blank values

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)

Partitioning with Analytics and Blank values

Author

Topic

hrishy
Starting Member

47 Posts

Posted - 2008-07-28 : 15:15:42

Hi

I have a data set like this


AccountNbr     RollNumber
1                 500
1                 
2                 400
2                 
3                 600
3                 700

i need to write a update statement that would make the dataset
look like below.


AccountNbr     RollNumber
1                 500
1                 500
2                 400
2                 400
3                 600
3                 700

one way of doing this would be analytics something like


select *
from (select *,
      row_number(),over(partition by AccountNbr
                        order by rollnumber desc) rn
      from my_table
      )
where rn>1

and then base my update on that sql .
However the above sql would produce a result set like


AccountNbr     RollNumber   Rn
1                 500        2
2                 400        2
3                 600        2

i dont want AccountNbr 3 to appear in the result set any idea how i can achieve this ?

regards
Hrishy

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-07-28 : 21:53:28

[code]
update a
set a.RollNumber = b.RollNumber
from
Mytable a
inner join
myTable b
on a.AccountNbr = b.AccountNbr
where b.RollNumber <> ''
and not b.RollNumber is null
and
(a.RollNumber = ''
or
a.RollNumber is null)
[/code]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-07-29 : 11:54:35

What is the logic behind the update? I see that most (IDs 1 and 2) get updated to the non-blank value, but ID 3 gets 100 added to the value.

Also, is there logic to exclude ID 3 from your select or can just use WHERE AccountNbr <> 3?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources