Deleting duplicate rows in Sybase

I had to delete a set of duplicate rows in Sybase and that had me in a quandry. I had done that many-a-times in Oracle using the ROWID psuedo-column but nothing similar is available in Sybase or for that matter in SQL Server. Here's how to go about this daunting task in Sybase!


The deletion of duplicate rows cannot be done in a single SQL query like it can be done in Oracle. Hence it needs to be done in 3 steps:

1. Create temporary table with one instance of each distinct row
2. Delete all rows having multiple instances
3. Move back the unique data from temporary table to actual table

Step 1:
Create temporary table with one instance of each distinct row with the below query.

select distinct t.* into temp_table_name
from data_table_name t
group by keycol1, keycol2, keycol3 
having count(0) > 1

Note: that this step creates the temp_table_name table so it will fail if a table by that name already exists.

This step puts one instance of each distinct row into the temporary table. However, some of the rows may contain identical values in the key columns but different data in the remaining columns. At this point you need to check the data in the temp_table_name and reconcile which of the rows you wish to keep for a given duplicate key value. You may even need to do these three steps on the temporary table to achieve a set of unique records in the temporary table.

Step 2:
Delete all rows having multiple instances with the below query.

delete data_table_name from temp_table_name 
where data_table_name.keycol1 = temp_table_name.keycol1 
and data_table_name.keycol2 = temp_table_name.keycol2 
and data_table_name.keycol3 = temp_table_name.keycol3

Step 3:
Move back the unique data from temporary table to actual table with the below query.

insert into data_table_name 
select * from temp_table_name

Remember to change the table & column names in the above queries.
Replace data_table_name with your actual table name,
temp_table_name with your choice of name for temporary table and
keycol1..n with the actual table column names!

Check out my previous post here for doing this same thing in Oracle.

Deleting duplicate rows in Oracle

Deleting duplicate rows in Oracle seems to be the simplest to me (may be because I have used it more or may be its quite simple really!). Lets get down straight to the code and do a recap of how to delete duplicate rows in Oracle.

Simple Solution
Note that this simple solution assumes that there are no primary or unique keys on the table but the combination of data in a set of columns defines the duplicates. The example shows three key columns, but you can use more or less columns to identify unique rows based on your actual data.

DELETE FROM data_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM data_table
GROUP BY keycol1, keycol2, keycol3

However, there could be cases where the table has a sequence / auto-generated column as the primary key. In such cases too, you can use the above query after substituting rowid with the id column name.

Advanced Solution
If you are looking for an advanced and faster solution with more features & flexibility, you can use the following solution that I found on devx. This solution uses RANK() function's capabilities to provide the extra features.

DELETE
FROM data_table
WHERE ROWID IN
(SELECT tmp_key
FROM
(SELECT tmp_key, keycol1, keycol2,
RANK() OVER (PARTITION BY keycol1, keycol2 ORDER BY tmp_key) AS seq_num
FROM
(SELECT rowid as tmp_key, keycol1, keycol2
FROM data_table
WHERE (keycol1, keycol2) IN
(SELECT keycol1, keycol2
FROM data_table
GROUP BY keycol1, keycol2
HAVING COUNT(0) > 1)))
WHERE seq_num > 1)

Let me break up this complicated query into parts and explain in detail.

1. The innermost sub-query (lines 12 - 15) finds those records that have duplicates. This gives us the sub-set of data that are only duplicate rows.
2. The next outer sub-query (lines 9 - 11) uses ROWD psuedo-column to create a single key column. This adds a key column to the set of data rows we are interested in.
3. The next outer sub-query (lines 6 - 8) uses RANK() function to dynamically assign sequence numbers to rows in the set of duplicate rows and orders them. Note that there are a set of sequence numbers each for each set of duplicate rows. This assigns a sequence number to and orders the rows inside each set of duplicates.
4. The next outer sub-query (lines 4, 5 & 16) selects the duplicate rows based on the sequence number assigned by the RANK function is above step.
5. The outer-most query (lines 1 - 3) deletes the duplicate rows.

Note: In case the table has a unique / primary key, substitute that instead of the ROWID psuedo-column in the above solution to get the same results.

Now, the extra features that using RANK provides are that you can choose the number of rows you want to keep. For example, you want to keep only the top 10 rows, you can change the numbers in lines 15 & 16 of the above query from 1 to 10 and add a proper ORDER BY to line 7 to get the data sorted in the required manner to determine the "top 10 rows".

Similarly, if you want to keep only the latest row, you can add a proper ORDER BY clause to line 7 to get the data sorted in the required manner to determine the latest row.

Remember to change the table & column names in the above queries.
Replace data_table with your actual table name and
keycol1..n with the actual table column names!

Check out my next post here for doing this same thing in Sybase.

Install WordPress on IIS7

A friend of mine wanted me help him install WordPress on his website - no big deal as WP is famous for it's 5 minute installs and so I figured this should be a cake walk for me. Little did I know that my friend's web-server was IIS7 on Windows Server 2008 and this would translate to a 5 hour install :(

Google, pointed me to a very good site that helped me out - trainsignaltraining.com.

Here are the components that you need to download:

• PHP 5.3.2
• MySQL 5.1.45
• PHPMyAdmin 3.3.1
• WordPress 2.9.2
• URL Rewrite Extension

Here are the Installation steps:

1. Install & configure PHP
2. Install & configure MySQL
3. Install & configure PHPMyAdmin
4. Install & configure URL Rewrite Extension
5. Install & configure WordPress
6. Configure Search Engine Friendly URLs on WordPress

However, here are a few "gotchas" that need to be taken care of:

• For PHPMyAdmin to work, you may need to change in config.inc.php
  $cfg[$i]['host'] = ‘localhost’; to
  $cfg[$i]['host'] = ‘127.0.01’; or
  $cfg['Servers'][$i]['host'] = ‘127.0.0.1′
• For PHPMyAdmin to work, you may need to change in config.inc.php
  controluser = ‘pma’ to
  controluser = ‘root’
• For PHPMyAdmin to work, you may need to change windows\system32\drivers\etc\hosts file:
  Remove the comment (#) in front of the line 127.0.0.1
  Comment out the line ::1 localhost
• Read the comments in all the pages as they have solutions to a lot of common problems

Note: I am assuming that IIS7 is already installed. If not, then there's a good tutorial for that as well here!

SQuirreL behaving weird on Ubuntu

I needed to access MySQL & MS SQL databases from my Ubuntu 9.04 laptop and was at a total loss. After a bit of research, SQuirreL sql client looked like the best choice.
However, since this is not available through the Synaptics Package Manager so it had to be downloaded and installed manually - which is the way software used to be installed on *nix boxes originally.

At that time, SQuirreL 3.0.2 was the latest version and all versions starting from 3.0 require Java6 - this I was able to install from the Synaptics Package Manager. Installing SQuirreL was simple: Run the command sudo java -jar squirrel-sql-3.0.2-install.jar and it was installed.

However I was unable to access MS SQL Server DB yet as I did not have the proper JDBC drivers for that. After narrowing down my choices, I found I was left with jTDS which is an open source 100% pure Java (type 4) JDBC 3.0 driver for Microsoft SQL Server (6.5, 7, 2000, 2005 and 2008) and Sybase (10, 11, 12, 15). Just put the jtds jar file in the squirrel install lib folder and then add the new driver from the UI.

After this, I was able to access MS SQL server but often, the program would just freeze or become unresponsive after a few minutes of use (or lack thereof). I would need to kill the java process and restart it. Also, the automatic updates never worked and so the software remained the same version till I decided to take things into my hands.

At this time the latest version available was 3.1 which was good news indeed - I was expecting a full solution to my problems! However, the website offered no documentation on how to upgrade the software, only how to install it. So I went ahead and installed it in a similar fashion - but this time it installed in a different folder. The previous install was in the folder /usr/local/SQuirrel SQL Client/ and the new one was in the folder /usr/local/squirrel-sql-3.1/

I then tried to uninstall the older version by running the uninstall.jar from the Unintall folder of the previous install. However that did not do much and so I just removed that whole folder - just like we used to remove software from *nix in the "good old days"! The good news was that the configuration was stored in my home folder in the .squirrel-sql folder so all aliases/connections/drivers etc were intact and I was able to use them immediately.

But alas! Even after all this effort, the problem of SQuirreL freezing after a few minutes of use still did not go away!

Please let me know if you see any solution to this problem or if you come across a better SQL Client for Ubuntu which can access MySQL & MS SQL!

Free Coffee this month (Feb 2010)!

During this cold February month, you can have a small cup of hot Coffee for free in Manhattan, courtesy SeamlessWeb!

Simply walk into one of the participating restaurants on any or all of the dates listed below and ask for a cup of a small coffee till stocks last! Generally the offer is available from 8am to 11am on weekdays only.

• Week of Feb 1-5, the participating stores were located in Midtown East.
• Week of Feb 8-12, the participating stores are located in Midtown West.
• Week of Feb 15-19, the participating stores are located in Upper East & West.
• Week of Feb 22-26, the participating stores are located in the Financial Di$trict.

Check out the participating locations on the website here.

Wearable Computers to have a "Sixth Sense"!

Wearable computers have a Sixth Sense - imagination or reality? Pranav Mistry of MIT Media Lab has created a device to exactly fit this bill!


Pranav Mistry is a PhD student in the Fluid Interfaces Group at MIT's Media Lab. He has invented a wearable system made out of off-the-shelf components that augment the way the wearer interacts with his environment. It consists of a web-cam, a small projector with a mirror, a connection to the cell phone (for internet connectivity) and four different colored finger caps for the gestures. He has named it SixthSense. According to him, it costs about $350 to build the hardware. The source code for the software has been put in the open by him releasing it under GPL.

This brings up a few technical questions:

• What is the hardware platform that is doing all the intensive computation?
• What language(s) have been used to create the software platform?
• How about the battery - all the devices require a lot of juice. Is it feasible to get good battery life over extended periods?
• What happens in cases where connectivity to internet is lost?
• Does it have ample local storage & intelligence built in, to work in offline mode and go online only when necessary?
• Does it sync up it's knowledge (stored locally) to & from the base computer?

And a few questions which are not so technical:

• Will this device ever be commercially available since the software has been GPL-ed?
• Will we become so dependant on such devices for our interaction with our immediate environment that we loose our human touch & OUR sixth sense?
• Would he have been able to create something as good had he continued his education in India?
• What else lies in future - will we become Humanoids?

It's very difficult to correctly & satisfactorily answer these questions. But while we ponder on the possible answers, check out this video that demonstrates the Sixth Sense Device at TEDIndia held at Mysore, India in November 2009.

The demo is just awesome - imagine what the final device/system will be when it is fully mature!

Creating good HTML emails

Creating good HTML email is a totally different ball game! All the rules/concepts that apply to creating good web pages do not necessary apply to creating good HTML emails at all.

In fact, none of the theories for creating good HTML web pages holds true for creating good HTML emails!
In this article, David Greiner gives a good idea what should and shouldn't be done. Incidentally, this validated most of the stuff that we had already been doing based on our experience with various webmails & HTML email blasts/campaigns that we worked on.

Here are the summary of points given by him:

• Use tables for layout and as containers for background colors
• Use nested tables instead of margins & alignments
• Set the width of each cell instead of at the table level
• Use pixels instead of percentage widths
• Use inline CSS always
• Design with the assumption that images will not be visible by default
• Use "alt" text for images
• Aviod spacer images
• Always use image widths & heights explicitly
• Prefer JPG & GIF images
• Test, Test & Test more with various clients...

And now, some more considerations for mobile emails:

• Keep the total width to less than 600 pixels
• Be aware & design with consideration that text may be resized
• Test, Test & Test more with various devices...

Read the full article by David Greiner on 24ways.org for the details and the code here.

Beautiful Forms using HTML5 and CSS

Even though HTML Forms are very boring & repetitive work requiring a lot of effort, they can be made really beautiful using the new features of HTML5 and a bit of advanced CSS.

First off, the Disclaimer:

• HTML5 is still a Draft and in the "Last Call" state in the WHAT Working Group.
• Not all browsers support HTML5 equally well - so expect varying results across browsers even for the same element
• Not all browsers support all elements/features yet
• Check out the list of differences between HTML4 and HTML5 here
• Check out the new features of HTML5 Forms here
• Wikipedia entry for HTML5 is here

Now that we have all those points out of our way, lets start with the actual stuff.
In this article on 24ways.org, Yaili explains in great detail how to use HTML5 and advanced CSS with CSS3 features to create really great looking forms.

Here are the most important points:

• Use fieldsets to for enclosing and grouping input elements.
• Use ordered lists to group each label+input pair of elements
• Use automatically generated counters using :before and :after psuedo-elements
• Dollops of CSS styling to make the form & its elements look really kewl

Here is the output as I see it in FF3.5.7 and IE7 on WinXP Pro - the differences are clearly noticeable. Also, the placeholder text does not show up at all!

FF 3.5.7	IE 7
Note the missing placeholder text, rounded edges and credit card images	Note the missing placeholder text, pointed edges and missing credit card images

Read the full article by Yaili on 24ways.org for the details and the code here.

Full Version of Software for a Buck?!

How would you like to have a licensed copy of a Full version of a software for 99 cents ?!

While scanning SD for deals, saw a post mentioning that the full version of WinPatrol PLUS will be available for just 99 cents on January 29th 2010! This lead me to the blog Bits from Bill written by Bill Pytlovany who created WinPatrol.

While this in itself is a very good deal for those still using Win platforms, the blog post contents got me thinking deeper. His blog points towards a paradigm shift in the Business Model - Songs are being sold for 99 cents a pop from the likes of Amazon, many iPhone apps are being sold for 99 cents each too. This banks on the human psychology/thought process of "what the heck - its only a buck..." to trigger impulsive buying and increasing sales. The same guy would have contemplated a hundred times had the price tag been say even $5! Could this be an industry saving business model?! Only time will tell ...

But in the meanwhile, make the most of it by taking the WinPatrol PLUS 99¢ license only on Friday, 29th January - note the icing on the cake that this license will be good for life! And read more about Bill's views on his 99 cent software experiment, the results of which will also be published on his blog over the weekend. I am all for this software pricing model as it is in the interest of the consumers!

HP USA Customer Service at it's best!

My HP Laptop adapter wire started to fray and then the adapter went bust in less than 10 months of ownership. And then the laptop just shutdown and would not boot up at all. Here is my (scary) experience getting it fixed.

I had a very harrowing experience with HP Support/Customer Service here in the US. Due to the adapter going bust, I suspect that the MoBo & RAM was fried on my laptop which was still under warranty. I called the Customer Support who sent me a pre-paid box to mail in the laptop. I mailed in my laptop & adapter and when called in to check the status, I was told that the adapter was bust due to my mishandling and so I would need to buy a new adapter myself. Also, since the laptop did not boot up with the adapter that I had sent in, it was being sent back to me as nothing could be done! And while we are discussing this, would you like to buy an original HP Adapter?

I just could not believe my ears and was really angry at them for this action. Couldn't they just use one of their adapters and confirm whether that was the only problem? They said that they would use only the items supplied and did not have any other "good" adapters with them. I talked to a few other guys and most of them told the same thing - but a few others did not have any idea at all and just read out the problem statement that I had mentioned in my initial call!

To top it all, all this was happening just a week before I was scheduled to take a 3 week vacation to India and 2 weeks before the warranty was going to expire - arrrggghhh!!!

At this point, I just lost all hope and left it to HIM to perform a miracle. And I was in for a pleasant surprise on Christmas eve - I received the repaired laptop with a brand new adapter in mail! The sheet accompanying laptop mentioned that the MoBo & RAM had indeed been replaced, HDD formatted & re-imaged and a new power adapter had been supplied :D

So, even though the Support/Customer Service did not have much clue, the Tech Service guys did a really good job. They did exactly what they should have done - replaced the busted parts as the laptop was still under warranty!

This is in sharp contrast to my experience with Dell Customer Service where-in everyone consistently told the same (correct) thing and the resolution was spot-on. I had a problem with the keyboard once and the adapter once and both times I was mailed in the replacement parts with clear details on what to do next.

Thankfully all's well - that end's well (atleast for now)!