T Sql Question - 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

General SQL Server Forums

New to SQL Server Programming

T Sql Question

Author

Topic

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2011-01-15 : 23:32:03

Dear Gurus,

I have a table like so

CREATE TABLE [dbo].[requestmaster](
[requestid] [nchar](8) NOT NULL,
[unit] [varchar](50) NOT NULL,
[requestno] [nchar](75) NOT NULL,
[requestdt] [datetime] NOT NULL,
[recddt] [datetime] NOT NULL,
[regndt] [datetime],
[unittype] [varchar] (50),
[seniority][numeric](3,0))

I want to set the seniority from 1 to .... n for each unittype.

That is the result would look like

Unit RequestId Seniority UnitType
9WG 20100001 1 Govt
18WG 20100006 2 Govt
20WG 20100007 3 Govt
8WG 20100009 4 Govt
12WG 20100005 5 Govt
VISHVESHWARAYYA INSTITUTE OF TECHNOLOGY 20110014 1 PvtEdu
AVINASHILINGAM INSTITUTE OF TECHNOLOGY 20110013 2 PvtEdu

The senrority needs to be updated each time a new record is inserted into the RequestMaster table.

How do I do it? Sould i accomplish it through atrigger on Sproc.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-16 : 05:25:45

[code]
update t
set t.seniority=t.rn
from
(select seniority,row_number over (partition by UnitType ORDER BY UnitType) AS rn FROM requestmaster
)t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2011-01-16 : 07:08:40

Dear Vishak,

I tried this. Getting an syntax eror near keyword 'over'

Thanks

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2011-01-16 : 07:11:11

Visakh,
I am using SQL Server2005.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-16 : 07:13:24

[code]
update t
set t.seniority=t.rn
from
(select seniority,row_number() over (partition by UnitType ORDER BY UnitType) AS rn FROM requestmaster
)t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2011-01-16 : 07:27:21

Visakh,

Could I use rank() func instead of row_number(). What wouls be the pros and cons.

Otherwise ur solution works for me.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-16 : 07:33:13

rank function will return same value for all the records with same unit type. for you what you need is sequence thats why i used row_number. if you want to use rank you need to order by means of a unique valued column.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kashyap.2000
Starting Member

22 Posts

Posted - 2011-01-16 : 11:39:01

quote:
Originally posted by visakh16

rank function will return same value for all the records with same unit type. for you what you need is sequence thats why i used row_number. if you want to use rank you need to order by means of a unique valued column.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi visakh,

Just like you have used update statement in your query in the same way how can we use delete statement ?

I mean following statement gives an error , can you please let me know how delete can be used here ?

delete from t where seniority=t.rn
from
(select top 1 row_number() over (partition by seniority ORDER BY seniority) AS rn FROM requestmaster
)t

Failures will either break you or will make you a better perosn....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-16 : 17:46:00

delete t
from
(
select seniority, row_number() over (partition by seniority ORDER BY seniority) AS rn
FROM requestmaster
) t
where seniority = rn

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

kashyap.2000
Starting Member

22 Posts

Posted - 2011-01-16 : 22:14:32

quote:
Originally posted by Peso

delete t
from
(
select seniority, row_number() over (partition by seniority ORDER BY seniority) AS rn
FROM requestmaster
) t
where seniority = rn

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

@Peso

Thank you sir.

I love My India.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-24 : 11:24:19

see what all you can do with row_number() function.

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources