cpradio's tidbits of information a one stop spot for my personal references


ROW_NUMBER() and PARTITION BY in SQL Server 2000

Here is the below SQL for SQL Server 2000 to select the latest version of a record grouping by a single column.

  FROM (
    SELECT *, (
        FROM MyTable AS counter
      WHERE counter.PartitionByColumn = MyTable.PartitionByColumn
        AND  counter.OrderByColumn >= MyTable.OrderByColumn
      ) AS rowNumber
    FROM MyTable
  ) AS r1
  WHERE r1.rowNumber = 1

Same code in SQL Server 2005 would look like this:

    ORDER BY OrderByColumn DESC) AS rowNumber FROM MyTable) AS rw1 
  WHERE rw1.rowNumber = 1


Mounting a partition from a Whole Disk backup

While backing up a family members hard drive (due to virus infestation), I realized I had backed up the entire drive instead of the partition containing their files. Instead of going through the backup routine again, I thought it would be useful to describe how I was able to mount the partition I wanted to backup so I could verify the files were all there.

First on the family members PC, I ran the following commad
fdisk -l [hard drive location]

This produced the following output (the section in bold is important):
Disk : 300.0 GB, 300090728448 bytes
255 heads, 63 sectors/track, 36483 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Next I ran another fdisk command on the computer were the whole drive backup image is stored to see how many bytes make a sector, based on the number of cylinders reported above.
fdisk -l -u -C 36483 [path to whoe drive image]

This will produce the following output (bold output is important):
Disk [path to whoe drive image]: 300.0 GB, 300090728448 bytes
255 heads, 63 sectors/track, 36483 cylinders, total 586114704 sectors
Units = sectors of 1 * 512 = 512 bytes
Disk identifier: 0x00000000

Device Boot Start End Blocks Id System
/dev/hda1 * 63 546209 273073+ 83 Linux

We then take the Start position (63) and multiply it by the number of bytes in each sector (512), which gives us 32256 bytes. That is the offset location of the partition we want to mount.

So now you can run the following command to mount the specific partition from your whole disk backup:
mount -o loop,offset=32256 [path to whoe drive image] /mnt


Kubuntu 9.04, KDE 4.3 and Quanta Plus 3.5

After upgrading to Kubuntu 9.04 and KDE 4.3, Quanta Plus was removed due to file dependencies not being met. To resolve the issue, perform the following steps (any 32 bit versions will need to find these packages built for 32 bit systems).

wget http://launchpadlibrarian.net/17069296/kxsldbg_3.5.10-0ubuntu1_amd64.deb
wget http://launchpadlibrarian.net/17069294/kommander_3.5.10-0ubuntu1_amd64.deb
wget http://launchpadlibrarian.net/17069293/klinkstatus_3.5.10-0ubuntu1_amd64.deb
wget http://launchpadlibrarian.net/17069291/kfilereplace_3.5.10-0ubuntu1_amd64.deb
sudo dpkg -i k*.deb
sudo apt-get install quanta