SQL: Finding all Duplicate Records in a Table

By Craig Lotter | Published: March 3, 2010

Sometimes it is quite handy to see which records based on a particular key appear more than once in a database. The trick here is that we are focusing on a particular column and deeming that should that particular key appear more than once in the table  then we have a duplicate scenario.

So let us look at constructing the necessary SQL statement.

For our example, let’s use a table called emails_sent which contains a whole lot of various columns including email_address, which will act as our key to search upon. So wanting to find all complete records for all email_addresses that appear more than once in the table, we put down:

SELECT *
FROM emails_sent
WHERE email_address IN (
SELECT email_address
FROM emails_sent
GROUP BY email_address
HAVING (COUNT(email_address) > 1)
)

Breaking this down, we’ll see that the SQL statement is actually made up of two distinct parts. The first part is the meaty one that is responsible for grabbing all the duplicate records, while the second part simply uses the result set stemming from this first segment to then return all the column values for all the duplicate records. Now while the second part is fairly simple, just a SELECT *FROM x WHERE key IN statement, the first segment deserves a bit more of an explanation.

What we are doing is grouping all email_addresses together that look the same using the GROUP BY clause. Next, we are applying a HAVING condition to the GROUP BY clause, this time asking that the only groups to keep are those for which the count of the group key is greater than 1. At this point the light should be blinking on and you should be shouting out, “Ah ha, so that is how it works”  in case you are wondering.

So the first part returns all the duplicate record keys and the second part then grabs all information for records that correspond on those initially returned duplicate keys.

Simple really.

We could of course through a simple modification to our HAVING clause change the SQL that it only returns records where the key only appears once – i.e. change HAVING (COUNT(email_address) > 1 to HAVING (COUNT(email_address) = 1.

To return the duplicate keys and the number of times that they appear we could use this:

SELECT email_addresses, count(email_address) AS numOccurrences
FROM emails_sent
GROUP BY email_address
HAVING (COUNT(email_address) > 1)

Posted in Tutorials | Tagged , , , , , | Leave a comment

Optimize Your Javascript by Minifying with Google’s Closure Compiler

By Craig Lotter | Published: March 2, 2010

Optimize your Javascript by minifying with Google’s Closure Compiler. Well, that pretty much says it all. By now we all no that there is plenty of scope for reducing the size of one’s Javascript code by replacing bulky variable names with shorter versions and stripping out whitespace, etc., but naturally as one would expect, achieving this optimization by hand is a rather tiresome affair.

Enter the nifty Google Closure Compiler, simply put, a tool for making Javascript download and run faster. It’s not a traditional code compiler mind you, it doesn’t compile source code into machine code but rather compiles Javascript to better Javascript, analyzing, clearing dead code, and rewriting and minimizing what’s left over. It checks syntax, variable references, types and even warns about common javascript pitfalls just for fun.

There are a number ways in which you can set the compiler loose on your code: you can use the open source java command line application, you can simply plug your script into the simple online web application or you can even make use of their full RESTful API.

The benefits of using this great little system do of course not need that much explanation. Firstly, in terms of efficiency, using the Closure Compiler will result in smaller javascript files which in turn means faster loading applications which obviously means reduced bandwidth needs. In terms of code checking, Closure Compiler provides warnings for illegal javascript as well as for potentially dangerous operations, resulting in less buggy scripts and scripts that are simply easier to maintain.

And just in case you were wondering why you should give them a spin, take note that jQuery have moved to the Closure Compiler to produce their minified scripts.

So what are you waiting for? ;)

Related Link: http://code.google.com/closure/compiler/

Posted in Software & Websites | Tagged , , , , , , , | Leave a comment

jQuery: Get all Selected Values or Text from a Multiple Select Listbox

By Craig Lotter | Published: March 1, 2010

Grabbing all the selected values or even text values from a multiple select listbox turns out to be quite simple if you know which tools to use.

The idea here is pretty simple. First we declare an array to hold our gleaned variables. Then we need to grab all the selected items in the listbox by making use of the :selected jQuery selector. Next we iterate through the selected items, using the standard val() to return the selected option’s value or text() to grab the actual display text that made up the list item.

Translating this all to jQuery code, we get this:

var realvalues = [];
var textvalues = [];
$('#multiplelistbox :selected').each(function(i, selected) {
    realvalues[i] = $(selected).val();
    textvalues[i] = $(selected).text();
});

And that’s pretty much it. If you check out the two arrays we just created then you’ll see we are now in possession of all selected values, as well as the selected list items’ display text.

Simple.

Posted in Technology & Code, Tutorials | Tagged , , , , , , | Leave a comment

SQL: Return all the Names when you have all the IDs

By Craig Lotter | Published: February 28, 2010

A silly one today, but worth recording here on my online memory bank because quite frankly, I forgot one could do it in the first place, and had to rely on Dave in the end to help me right.

So here’s the problem.

I have all the ID values for wildlife parks but instead of displaying the ID values I want to display the park names. These are stored in a table that has two fields, namely ID and the corresponding display name.

Now sure I could loop through each ID and use a plain and simple SELECT displayname FROM parks WHERE parkID = 1 or whatever the ID value is to return each and every name, but I want to simplify this into a single SQL statement because I’m feeling a little lazy today.

So how can this be done?

Well the trick here is to swap out the = operator for the in set operator, meaning we now have a sql statement that looks like this:

SELECT displayname FROM parks WHERE parkID IN (1,2,13,14,21,7)

This will evaluate true for each record where parkID falls into the designated ID set, returning the display name associated with that particular record retrieved record.

Nice. So from running multiple SQL select statments to executing just one, I’d say we’ve solved today’s little problem! :)

Posted in Technology & Code, Tutorials | Tagged , , , , , | Leave a comment

Remove all Listbox and Dropdown List Items with jQuery

By Craig Lotter | Published: February 27, 2010

I’ve said it once and I’ll say it a million times over and again, the jQuery javascript library is simply fantastic. Today’s little code snippet is going to look at removing all the options from a select dropdown list or listbox, on the fly and using jQuery.

To get this little trick working, we are going to be making use of the remove() function that allows us to strip the target out of the pages DOM, essentially removing it from view and access.

In order to do this, we will construct a selector that will locate the target list using its ID value and then filter it down to all of its option children. Traversing through each option using the handy each() function, we will then apply a remove() call, essentially dropping that specific option out of the list, and in the end, leaving us with an empty <select> construct. Nice.

So the snippet to do this then would look a little something like this:

$('#selectlist option').each(function(i, option){ $(option).remove(); });

You can probably simplify the above by shortening the call logic and letting jQuery handle all the internals for you, but leaving it in the form that the example is currently in shows you exactly step for step how we are setting about achieving our desired empty state.

Magic I tell you.

Posted in Technology & Code, Tutorials | Tagged , , , , , | Leave a comment

jQuery: Select all options in a Multiple Select Listbox

By Craig Lotter | Published: February 26, 2010

Sometimes it is nice to present a multiple select listbox to a user with everything already selected.

Handy if you’re using listboxes as filter controls, and something that is, as per usual, quite easy to do using the magic that is the jQuery javascript library.

Essentially what the aim of the game here is to set the selected option in each of the multiple select listbox’s <option> entries.  In order to do this, we will once again make use of jQuery’s nifty attr() function and apply this to the result of our clever little jQuery selector call, which will hone in on our desired select listbox using its ID value and then extending that result to only include all option entries.

So the resulting call would look something like this:

$("#multipleselect option").attr("selected","selected");

And there you go. A multiple select listbox with everything already selected for you!

Posted in Technology & Code, Tutorials | Tagged , , , , | Leave a comment

How to Disable a Button with jQuery

By Craig Lotter | Published: February 25, 2010

Disabling a button on a webpage using jQuery is remarkable simple – just set the selected button’s disabled attribute to true, in other words, make use of a .attr(‘disabled’,true) function call.

This can then obviously be extended to disabling all buttons on a webpage using the following snippet of code:

$('input[type=button]').attr('disabled', true);

Like I said, remarkably simple. (Obviously to enable you simply set the attribute to false again!)

Posted in Technology & Code, Tutorials | Tagged , , , | 1 Comment

Fixing a Broken Windows Automatic Update in Windows XP

By Craig Lotter | Published: February 24, 2010

My old laptop (purchased from my old job when I left the University of Cape Town) is still sporting a pretty ancient copy of Windows XP and unfortunately thanks to the various policies and whatnot Commerce I.T. implements on their roll-out equipment, my particular version of Windows Automatic Update simply didn’t work any more.

Great if you don’t particularly like that “I need you to waste all your Internet cap on update downloads” notifications that seem to pop up all the time, but definitely not so great if you realize those updates are actually pretty damn critical when it comes to your online security!

Now not eager to delve into the bowels of my registry once again, I instead turned to the Internet to locate an application that could fix this particular problem for me… and came across the brilliant little DjLizard (Michael Cooke) creation called Dial-a-Fix.

Now according to the site, “Dial-a-fix is a collection of known fixes gleaned from Microsoft Knowledgebase articles, Microsoft MVPs, and other important support forums” and once you load up the simple .exe (no installation required), you will see that it has indeed been designed to repair problems encountered with SSL/Cryptography, Windows Update, Microsoft Installer and various miscellaneous shell problems. Fixing problems like my buggy Windows Update turned out to be as simple as clicking the main Windows Update checkbox and hitting GO!

Mousing over any of the checkboxes or buttons gives you a full list of what DLLs or services are going to be registered, restarted, etc. and in a nutshell, pretty much explains what Dial-a-Fix does: stops services, installs selected software, registers DLLs, restarts services, and removes several rogue policies.

In other words, a lifesaver application.

Oh, and it is designed to work on most 32-bit versions of Windows XP in case you are wondering.

Download the application and use at own risk here.

Related Link: http://wiki.lunarsoft.net/wiki/Dial-a-fix

Posted in Software & Websites | Tagged , , , , , | Leave a comment
 
Your Ad Here
Afrigator   Sorted bru, tagged by Amatomu.com