Rank(),Dense_Rank(),Row_Number() Syntax

The following post is about Rank(),Dense_Rank() and Row_Number() T-SQL Commands.

declare @t table(FranchiseName varchar(200),AssociatedCity varchar(20),Sport varchar(200))
Insert into @t values
('Mavericks','Dallas','NBA'),
('Knicks','New York','NBA'),
('CowBoys','Dallas','NFL'),
('Gaints','New York','NFL'),
('49ers','San Francisco','NFL')

;with CTE AS
(select *,Row_number() Over(Order by AssociatedCity) as [RowNumber] from @t T)
select * from CTE

;with CTE2 AS
(select *,RANK() Over(Order by AssociatedCity) as [Rank] from @t T)
select * from CTE2

;with CTE3 AS
(select *,DENSE_RANK() Over(Order by AssociatedCity) as [DenseRank] from @t T)
select * from CTE3

Advertisement

Changing the SQL Agent Job properties in one go

I was installing a new version of a vendor application and one of the requirements was to change the existing sql agent job owners to a different user account.

There were several jobs and I needed to find a way to script them out and do it all at once and all these jobs had the same naming convention, so,it was possible for me to do wild character search. Below is how I did,

Select
'EXEC msdb.dbo.sp_update_job @job_id=N'''+cast(Job_ID as varchar(100))+''',
@owner_login_name=N''NewJobOwner''' from msdb.dbo.sysjobs A
INNER JOIN master.dbo.syslogins B on A.owner_sid=B.sid
where A.enabled = 1 and A.name like '%--NAMEoftheJOBS--%' and
B.loginname= '--OldJobOwner--'

Copy the output of this into SSMS and Execute.

Alternatively, I could use loop or cursor and update one by one but I liked the above method.