Twitter

Follow SQLQuill on Twitter

Subscribe

MN Pass

How to: Number Rows – Grouped by a Column – TSQL

There are quite a few times I need to number rows of data based on some grouping column.  I often forget how to do it.  Well, here’s a couple of examples:

Create table TestTable1
(ID int,
InsertDate datetime)
Go
Insert TestTable1 (ID, InsertDate)
Values(1,'8/1/10'),
(1,'7/1/10'),
(1,'6/1/10'),
(2,'3/15/10'),
(2,'3/16/10'),
(2,'3/13/10'),
(2,'3/11/10')
--Note the values are out of order for ID 2
Select *,
(select count(*) from TestTable1 TT2
where TT1.ID = TT2.ID
and TT1.InsertDate < TT2.InsertDate) as 'rank'
from TestTable1 TT1
Order by ID, rank

Or a simpler way…

SELECT *,
ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY insertdate ) as 'rank'
from TestTable1 TT1
Order by ID, rank

Here are some examples and explanations:

August 31st, 2010 | Tags: | Category: HowTo, Scripts | Leave a comment

How to: Find the restore history of a DB – TSQL Style

There’s been many times I’ve tried to find the restore history of a database.  Questions like when was the backup taken?  Can we re-restore and re-do this?  Can we create a copy of the original database?  etc.  Most of the times I use jobs to restore, but sometimes I’ve got to use LiteSpeed, etc.  I had to find the source of a backup this morning – here’s what I came up with for a script:

Declare @DBName varchar(255)
Set @DBName = '<<Database Name Here>>'
Select top 10
     destination_database_name,
     RH.restore_date,
     BUS.server_name as 'SourceServer',
     BUS.database_name as 'SourceDB',
     BUS.backup_start_date,
     BUS.type,
     BMF.physical_device_name
from msdb.dbo.restorehistory RH
Join msdb.dbo.backupset BUS on BUS.backup_set_id = RH.backup_set_id
Join msdb.dbo.backupmediafamily BMF on BMF.media_set_id  = BUS.media_set_id
where RH.destination_database_name = @DBName
Order by RH.restore_date desc

Note – Here’s Technet’s documents on the Backup and Restore Tables in MSDB.

August 25th, 2010 | Tags: , | Category: HowTo, Scripts | Leave a comment

How to: tell when a server is back up after a reboot

I learned this a long time ago.  It helps when building multiple servers, you’ve got a deadline and you need to reboot one or more.  Simply, fire up a command prompt and add the –t switch to the end of the ping command.

Ping ###.##.##.## –t

or

Ping <<ServerName>> –t

The –t will continually ping the server until you stop it (with Ctrl-C)….So after the server shuts down, you’ll get a Request Timed Out – every second until it’s back up.  Once you start seeing a response, it’s on the network – it may take another minute or two to be able to RPD to it.

August 20th, 2010 | Tags: , , | Category: HowTo | Leave a comment

How to: find the name of a server from an IP address

About 8 years ago I worked with a guy who taught me a lot of non SQL IT stuff.  He always explained things very well – if I asked.  He taught me how to find a server’s name by using the ping command and a switch.  I couldn’t remember how we did it and every time I’ve looked up how to do it, I couldn’t figure it out – until today.  Open a command prompt and:

Ping -a ###.##.##.##

Note the –a resolves addresses to hostnames.  Also, the –a needs to be in the front of the IP!  I think that’s the part I couldn’t get figured out.  I’ll now have it on my blog if I ever forget again.

August 19th, 2010 | Tags: , , | Category: HowTo | Leave a comment
« Newer Entries