Search The Web

Today's Headlines

Monday, November 30, 2009

Microsoft Access Tips & Tricks: Select Random Rows

Sometimes, one has a need to select random rows from a table. One may want to run a sweepstakes or lottery based on selecting winners at random. Or one might require a random sample of data for model development, with the rest of the data being used for testing the model so developed.

You can find other Microsoft Access tips and tricks in these earlier posts: Medians, Modes, Geometric and Harmonic Means, and Ranking of each row in a query.

Microsoft Access has no built-in way to generate random rows. In general, a query that does not have an ORDER BY clause is supposed to pull out random rows, but in my testing, that never works in Access to pull out random rows. But, there is a way to explicitly get Access to pull out random rows using a query.

The trick to doing this is a function called rnd(). This function generates random numbers. Read the Microsoft Access help files for a good description and technical details of how the function works, paying particular attention to the return value for different values of its argument.

Now that we know what the function does, how do we use it to pull out random rows from a table? The trick is essentially to associate a random number with each row in the table, then pull out the top few rows (as many random rows as are required) by ordering the rows by the random number associated with each row.

In fact, if your table was designed with a random autonumber field as the primary key, that would be precisely what we would do, and we could actually be done. Your SQL might look something like below:

SELECT TOP 5 * from myTable ORDER BY myRandomAutoNumberField

The above query pulls out the rows with smallest values in the field myRandomAutoNumberField (remember that you are sorting in ascending order, and TOP 5 pulls out the rows that would appear at the top of the query, so they would be the rows with the smallest values of the ORDER BY field). The * tells Access to pull out whole rows (including the autonumber field, in this case) rather than a subset of fields. You can replace * with the subset of fields you want pulled out if you only want a subset of the fields.

If we wanted a 10% random sample of the data rather than 5 rows, you could use SQL like below:

SELECT TOP 10 PERCENT * from myTable ORDER BY myRandomAutoNumberField

There is a big disadvantage to this approach, though: Every time you run the query, you will get only the same exact rows pulled out. Since the random autonumbers are permanently associated with each row, there is no variation from run to run that would result in any change in the actual rows pulled out by the query. The results are entirely predictable and may not work well for something like a raffle in which multiple random samples need to be pulled out for multiple prizes.

Using a dynamic random number generator to generate the ranks would take care of this disadvantage. It would enable you to pull out random rows that change each time the query is run so that the results would not be so predictable.

Your first thought might be to write a query as below:

SELECT TOP 5 * from myTable ORDER BY rnd(10)

The SQL looks fine, and in fact, is syntactically correct. As you can tell from reading the help file on rnd(), a positive number like 10 is a good choice for generating streams of random numbers. But if you run this query, you will actually see that the set of rows pulled out is the same every time your query is run. Try it and you will see that it is true.

Why is that? To answer that question, we need to see exactly what our query is doing. The query is ordering rows by the random number returned by rnd(10), and then returning the first 5 rows based on that ordering. Unfortunately, the query actually associates only one random number with each row of the table, not a different random number with each row of the table. Surprising but true. To see this, run the following query:

SELECT *, rnd(10) as myRandomNumber from myTable ORDER BY rnd(10)

You will see that the numbers in the calculated field, myRandomNumber, are the same for each and every row of the result set produced by the query. The reason is that Access is too clever for its own good! You see, since rnd(10) is a function of a constant number that has nothing to do with any field in a table that changes from row to row, the SQL optimizer of Access decides that it will execute rnd(10) just once, and append the result to every row of the query rather than running the function once for each row.

Now, you and I know that the function actually produces a different result for each row if it is run once for each row. But Access thinks that a function can only produce a constant result if the parameters passed to the function do not involve any change from row to row. As I said, the SQL optimizer in Access is too clever for its own good.

But, it is easy enough to force Access to run the random number generator once for each row pulled out by the query. All we have to do is pass an argument to rnd() that changes with each row so that the SQL optimizer in Access can't try to take any shortcuts when trying to optimize the query.

The easiest way to accomplish this is to pass one of the fields in the query as an argument to rnd(). You just have to make sure that the values in the field are all positive numbers. Remember that if you pass an argument to rnd() that is zero or a negative number, the function either returns the same random number or the most recently generated random number. The only two options for generating the next random number in the sequence is to pass rnd() a positive number as its argument or pass it no argument at all. Passing it no argument is not an option since the silly SQL optimizer would once again get in the way!

So, if you have a field in the table that is guaranteed to be a positive number in every row of the table, you are done. You can write SQL like below and you will get your random records without any problem:

SELECT TOP 5 * from myTable ORDER BY rnd(myPositiveField)

or

SELECT TOP 5 PERCENT * from myTable ORDER BY rnd(myPositiveField)

We are almost there. What if your table is missing a suitable field for the query above? In other words, you have no field that has positive values in every row of the table. Never fear! There is a way around that too! Remember that rnd() returns a positive number between 0 and 1 every time it is called. So, you can actually use rnd() to generate a number that is guaranteed to be positive, and pass that as an argument to another call to rnd() to generate the random numbers that will be used for ranking!!

So, suppose you have a numeric field myNonNullField in the table which contains positive, negative or zero-valued numbers in each row of the table. As long as myNonNullField is guaranteed to not contain any NULL values, The SQL statements below can be used to select random rows from the table:

SELECT TOP 5 * from myTable ORDER BY rnd(rnd(myNonNullField))

or

SELECT TOP 5 PERCENT * from myTable ORDER BY rnd(rnd(myNonNullField))

The latter would obviously be used if you wanted a random sample that contained a fixed percentage of the total number of rows in the table rather than just a fixed number of random rows.

The field myNonNullField in the above queries is some numerical field that can contain, positive numbers, zeroes or negative numbers. In fact, as mentioned above, the only condition is that myField not contain any NULL values. Rnd() does not play well with NULL arguments, so it will create a run-time error if myField contains NULL values in any rows.

To get around this new problem we will use Access's nz() function. Nz() returns a zero by default when fed a NULL argument. When it is fed a numerical argument that is non-null, it returns the same value as the return value. By default, it returns a zero when fed an argument that is null (hence the name nz(), which stands for null-to-zero), but you can get it to return any value you want by feeding the function an optional second argument. You can read full details about the nz() function also in the Access help files.

Now, we are ready to solve our problem even if there is no field in your table that contains non-null values in it. The SQL below uses a daisy chain of 3 function calls to generate random numbers to order the rows pulled out by the query:

SELECT TOP 5 * from myTable ORDER BY rnd(rnd(nz(myField)))

or

SELECT TOP 5 PERCENT * from myTable ORDER BY rnd(rnd(nz(myField)))

The only condition now is that myField be a numerical field in the table myTable. Now, there are conversion functions in Access to convert between most types of fields. So, even if your table does not have any numerical fields at all (which would be highly unusual, but not impossible), you just have to use an appropriate conversion function as the innermost link in the daisychain of function calls above to get an appropriate random number to order your rows by. You can read about conversion functions also in the Access help files.

Hope you found the technique explained in this post useful for your needs! If you have any questions, concerns or comments, please do feel free to use the comments field to let me know. If there are other aspects of Microsoft Access or SQL you would like me to address in future lessons, let me know too. Good luck!!

Sunday, November 29, 2009

Hope You Had A Happy Thanksgiving!

I hope everyone who celebrates Thanksgiving had a happy Thanksgiving! And for those who don't, hope the past year has given you something to be thankful about!!

It was a short work-week because of the long 4-day weekend associated with the Thanksgiving holiday. There were lots more meetings, and in fact, I could not get out of work until almost 3 PM on Wednesday even though most other departments at my workplace let everyone go by about 1 PM to kickstart the holiday weekend.

The argument about whether and how to build a prototype of our project was still ongoing. It may not get resolved in the coming week either. To be perfectly honest, I may not be so opposed to building a prototype to demonstrate the capabilities of the model we were building if I was convinced that it was my manager or director who needed to be convinced about the viability of doing it.

The problem was that they were bending over backwards to play a political game in which some unqualified half-wit from our IT department demands that we take outrageous steps to "prove that our model works", and we just go along with that because we don't want to take a stand against them. I wouldn't even resist it so much if my manager and director told me honestly that this was indeed what was happening and that I should go along just to help them out, at least temporarily. No, they continue insisting that it is now my department's internal policy that all projects have a prototype built as part of the project to prove that "we know what we are doing" before going on to the full project. And this after our department has had a 20-year record of delivering working models by hook or by crook. To insinuate now that somehow we have suddenly stopped "knowing what we are doing" is insulting and deeply offensive to me. So, as long as they insist on pushing, I fully intend to push back with as much force as I can politically afford to!

It was not always this way. At one point, our department had its own complement of IT professionals who reported to our own managers and director. They worked with the modelers on all projects and were vested in seeing to it that the projects succeeded. They had no other loyalties or political battles to fight, so IT support for our projects was never an issue.

Then, they decided to "reorganize" things, probably because things were working too well. They separated the modelers from the IT professionals and put the latter in the IT department. The IT department absorbed them, reassigned them away from our projects and in some cases laid them off when their budgets were tight. End result: the bungling IT department controls what our department does because they know that our department can not deploy any model without their support in providing data, putting up a user interface, etc. It has been nothing but trouble since the reorganization, and I have been pushing my manager and director to hire our own IT professionals again so that we can wash our hands of the IT department and its incompetents. But it doesn't look like that is likely to happen anytime soon.

Anyways, be that as it may, things at home went pretty well this week. As I had mentioned last week, I did not have my weekly karate class this week. Instead, we went to a pre-thanksgiving party at an acquaintance's home. It was a potluck party. Some of the dishes were very good, others were so-so. The party itself was not much fun either because the conversation was monopolized by a few loud-mouths that had no clue what they were talking about.

I like to socialize at parties when the company is right, but my standards for the "right company" are somewhat exacting. I don't like people who want to talk for the sake of being heard. Most such people don't know what they are talking about most of the time anyways. It is easy enough to ignore one or two such people at a party if the others are somewhat sensible. But unfortunately, this party had more than its fair share of idiots. After dealing with idiots at work all week, I was too exhausted to put up a fight with any more at this party, so I tuned them out completely, and pretty much spoke only when spoken to. As George Bernard Shaw would have said, I enjoyed myself at the party because there was nothing else to enjoy!

On Thanksgiving day, we went out to lunch with a different group of friends. The food at the restaurant was very mediocre, especially given the price we paid for it, but the company was good. I had a lot more fun at this get-together, and we ended up spending almost 3 hours in the restaurant.

Black Friday lost its charm for me quite a few years back. I have not really wanted anything badly enough to stand in line for 5 or 6 hours to begin with. And the deals were getting worse and worse too. I guess some people still choose to camp out in front of stores and do other stupid things because they will have something to talk about with their friends. I had scanned the Black Friday ads online during the week, and I did not find anything that would cause me to lose sleep, so I rolled out of bed slowly at around 10 AM on Friday.

My wife went out to do some shopping around 9 AM because she had some coupons. She came back with some snowboots after I woke up, and tried them on the kids. One of them fit perfectly, but another needed a different size, so I went out to the store to find a replacement. While there, a GPS unit caught my eye, and had a very attractive price, so I decided to pick it up on a whim.
Nextar X3-11 GPS
This was regularly priced at $199.99, but was on sale at Kohl's for $89.99. In addition, it had a $20 mail-in rebate just on the 27th of November. I also had a 15% coupon for all items at Kohl's, and Kohl's was giving its customers a $10 coupon for every $50 in purchases at the store. So, I was getting this GPS unit for just over $45 + tax after taking into account all the discounts, rebates, coupons, etc.

I had resisted getting a GPS unit for my car because I was not sure they were that useful. I had seen them in action before, and I was not bowled over by them. Some of them took a while to start tracking, others had problems with the routes they came up with. They went out of date pretty quickly and the manufacturers had no incentive to keep updating them because they made a lot more money selling new units rather than updating old ones.

But the most important reason I never bought one was that they were thief magnets if you left them visible in your vehicle, and even though I did not care if the GPS unit was stolen since they were usually only a couple of hundred dollars, the collateral damage to the vehicle could be far more expensive.
TomTom XL 330S Special Edition On-The-Go Bundle Pack
Obviously, this $45 GPS would also be a thief magnet (if only the thief knew what a cheap unit it was! Hey, maybe I will stick the price tag to the unit and see if that deters would-be thieves!!), and it may have other problems too. But I decided I would at least try them out to see if things have gotten better. And this unit came with its own carry-pouch and so on, so if I remembered, I could carry it with me rather than leaving it mounted in the car.

After I bought this unit, I decided to stop by a different store and pick up three more GPS units of different brands that were on sale. My logic was that if I was willing to spring for a GPS unit, might as well go for the best one out there that fits within my budget. Since, I did not have access to online reviews and other resources at that point in time, I decided I would have to pick the units up when they were available, then evaluate them at home and return what I did not want. On the fly, I set my budget at $100, so I picked up units that cost $79, $89 and $99.
Magellan SE4 GPSTomTom One 130 GPS
The four GPS units I picked up were the Nextar X3-11, Magellan SE4, TomTom One 130, and the TomTom XL 330S bundle pack. In the next couple of weeks, I will probably do extensive online research, do some real-world testing of each of the units (this is a bit of a problem because, typically, I don't travel by car extensively), decide on which unit to keep, and then return the other three. So, I ended up contributing to the recovery of the economy even though I had not planned on it originally! I hope that gives somebody else something to be thankful about!!

I was debating about whether to do it, but ultimately, I stuck to my regular weekly schedule, and did put up a few blog posts in spite of this being a short work-week. I completed my research on coming up with a partially hidden FlagCounter widget for my blog that would enable me to advertise the range of countries from which I have visitors without revealing how many visitors I get. Based on the research, I came up with something that should satisfy people who have needs similar to mine. I posted a followup to my post on gadgets that bloggers would find useful, by writing up my modification of the FlagCounter widget so that my traffic stats are kept private. By the way, as of this writing, my widget has 62 country flags on it. FlagCounter also provides a state flag widget even though development and improvement on it has been discontinued. As of this writing, I have also had visitors from 40 different states of the USA.

I also published a post on funny street signs, another on Microsoft Access to rank every row pulled up by a query, and another on Vedic Mathematics. I have written up a couple of posts for the upcoming week, so, now it is time for me to finalize them, and also write up one on Vedic Mathematics for the upcoming week. In the next few weeks, I will also post my first-hand experiences with the GPS units I have bought, and intend to try out. Hopefully you will stick around to find out what my final verdict is!

Thursday, November 26, 2009

Microsoft Access Tips & Tricks: Rank Every Row

In previous lessons in this series, we have talked about the calculation of statistical measurements pertaining to a field in a table. We have covered the median, the mode, and the geometric and harmonic means of a field. In this lesson, we will deal with a different kind of problem that does not involve producing a single number as the answer, but rather involves modifying every row of output from a query.

Sometimes, in a query, you want every row to have a row number (or rank) associated with it. You may need this simply to figure out what the rank of a particular row is based on certain sorting conditions. Some reports also have a need for such ranking because they use the rank to color alternate rows in the report dark and light so that the report becomes easier to read.

Notice that even if your table has an autonumber field (and this autonumber field is not set to random), you can not be guaranteed that this autonumber field can serve as a good substitute for a rank. First of all, the autonumber field denotes the order in which the rows were entered into the table. This may not be the same order in which the query pulls the data out, depending on the ORDER BY clause used in the query. Thus, the values in the autonumber field could be all over the place instead of increasing steadily in the query results.

Moreover, autonumber fields can have gaps (when rows are deleted from the middle of a table, Access never fills those gaps with new data). So, your data will also have gaps in the rank, which may be undesirable. More importantly, depending on the subset of rows pulled out of the table, there is no guarantee that the ranks will start with 1 and go to the number of rows in the query results. Thus, figuring out how to generate ranks on the fly inside SQL can come in very handy even if the table has a field that could be used as a rank under some limited conditions.

If the query pulls out unique rows (i.e. no two rows have identical information on them, and you can always tell one row apart from another in the query's results just by looking at the results of the query), then attaching a rank to each row in the output is very easy. You can also guarantee that these ranks are unique without having to jump through any hoops!

If the query does not pull out unique rows (i.e. all the data is identical between two or more rows), then you can still attach a row number (or rank) to each row, but these row numbers or ranks will not be unique if the ranking is based purely on the information being displayed. There is a way to overcome this if you do need unique ranks for the rows, as long as the actual data in the table is not duplicated across rows. We will talk first about the easy case where the rows are unique, then we will talk about the options for queries that pull out non-unique rows.

If the query does pull out unique rows, then you can easily attach a row number to each row after you sort the query using the ORDER BY clause of SQL. There are actually two ways of assigning a rank to each row as shown below, and both the methods produce identical results. Let us first deal with the case where our query produces output with just one field from the table. Let that field be myField. The two SQL constructs below will produce ranks for each row in the query results. Both the constructs produce identical results, so you can choose to use whichever one catches your fancy!

SELECT (SELECT count(*) from myTable where myField <= T.myField) as Rank,
myField from myTable as T ORDER BY myField

SELECT (SELECT count(*) + 1 from myTable where myField < T.myField) as Rank,
myField from myTable as T ORDER BY myField

In the subquery inside each of these queries, we are counting the number of rows which have a value of myField less than (or less than or equal to) the value of myField we are dealing with in the outer query. We are then using that count + 1 (to account for the current value of myField), or the count itself (if the comparison is less than or equal to since less than or equal to would include the current value of myField) as the rank for the row.

If the ordering in the main query is in descending order, it is easy to modify these queries to take that into account simply by changing the comparison sign inside the subquery as below:

SELECT (SELECT count(*) from myTable where myField >= T.myField) as Rank,
myField from myTable as T ORDER BY myField desc

SELECT (SELECT count(*) + 1 from myTable where myField > T.myField) as Rank,
myField from myTable as T ORDER BY myField desc

What happens if we are pulling out a subset of rows in the table based on some filtering criteria? Suppose we use some condition C (which is a boolean construct that determines whether a given row is part of the result set or not) to select rows for inclusion in the results of the query. The SQL below shows how to rank rows in such a query:

SELECT (SELECT count(*) from myTable where (myField <= T.myField) AND (C)) as Rank,
myField from myTable as T where C ORDER BY myField

SELECT (SELECT count(*) + 1 from myTable where (myField < T.myField) AND (C)) as Rank,
myField from myTable as T where C ORDER BY myField

If you want to rank based on the entire table, but want to attach the ranks to the subset you pull out based on some filtering criteria (for instance, you want to find out the ranking of the first row in your subset when compared with all the data in the table), you can omit the condition C from the subquery. But note that if you do that, the ranks attached to the rows in the subset pulled out by the query may not be contiguous and may not run from 1 to the number of rows pulled out by the query.

The SQL construct we have been discussing can be extended to cover cases where you need to pull out multiple fields from the table. Once again, we will deal with the case where the query produces unique rows as its output. The method relies on the ORDER BY clause once again. The outer query is similar to the outer query used in the previous case, but notice that the subquery used to generate the rank is a little different.

As the number of fields in the query goes up, the WHERE clause of the subquery has more and more parts added to it, so it can get pretty long if the query pulls out lots of columns! With just two columns pulled out, the query looks as below (I have only provided the version of the query that uses <= in the comparison. It is easy enough to modify it to use < and increment the count by 1 if needed):

SELECT
(SELECT count(*) from myTable WHERE
((myField1 < T.myField1) OR
((myField1 = T.myField1) AND (myField2 <= T.myField2)))) as Rank,
myField1, myField2 from myTable ORDER BY myField1, myField2

With three fields being pulled out, the query looks as below:

SELECT (select count(*) from myTable where ((myField1 < T.myField1) OR
((myField1 = T.myField1) AND (myField2 < T.myField2)) OR
((myField1 = T.myField1) AND (myField2 = T.myField2) AND (myField3 <= T.myField3)))) AS Rank,
myField1, myField2, myField3
FROM myTable AS T
ORDER BY myField1, myField2, myField3;

Notice how any row that was ordered before the current row under consideration in the main query will fall into one and only one of the conditions linked by OR's in the subquery. This is important in ensuring unique ranks for all the rows in the main query.

The other important thing to note is that the ORDER BY clause of the main query should include every field that is included in the query so that the ranking is done at the row level rather than a subset of the fields in the row. If the ranking is done on a subset of the fields output by the query, this query will produce unique ranks only if the rows are unique with respect to just the fields that are included in the ORDER BY clause of the query.

It should be relatively easy to extend the SQL above to a case where a condition is used to filter rows for inclusion in the results of the query. Just remember to include the same condition in the subquery as in the outer query, and link it with the condition already in the subquery (for generating the rank numbers) with an AND. Use parentheses generously since you don't want the condition to be linked to just a part of the condition already in the subquery, but with the entire condition already in the subquery.

What if the ordering of the main query uses ascending order for some fields and descending order for others? Let us rewrite the last query above (which pulls out myField1, myField2 and myField3) such that myField1 and myField3 are sorted in ascending order, and myField3 is sorted in descending order. Then the query would look as below:

SELECT (select count(*) from myTable where ((myField1 < T.myField1) OR
((myField1 = T.myField1) AND (myField2 > T.myField2)) OR
((myField1 = T.myField1) AND (myField2 = T.myField2) AND (myField3 <= T.myField3)))) AS Rank,
myField1, myField2, myField3
FROM myTable AS T
ORDER BY myField1, myField2 desc, myField3;

Notice how the comparison involving myField2 has the direction reversed, to correspond with the fact that its ordering is reversed. The rest of the query remains pretty much the same. That wasn't too hard, was it?! I didn't think so!!

Now, let us turn our attention to queries that pull out non-unique rows. For instance suppose the myField field in myTable had the data elements 1, 2, 3, 3, 4, 4, 4, 5 in 8 rows. Now you can use the SQL below to generate ranks for the rows, but the ranks will not be unique:

SELECT (SELECT count(*) from myTable where myField <= T.myField) as Rank,
myField from myTable as T ORDER BY myField

This is obviously the same SQL we used to generate unique ranks in queries that pull out unique rows. But since the values are not unique in this case, this will actually produce the query result below:

Rank myField
1 1
2 2
4 3
4 3
7 4
7 4
7 4
8 5

As you can tell, the ranks are not unique and they have gaps. You can be guaranteed that the rank numbers will go up to the number of rows in the result set. But you can not be guaranteed that the ranks would start at 1. In fact, if there were 8 rows in the results and all of them had the exact same value in myField, this query would assign a rank of 8 to each row.

Because of the <= comparison in the subquery, non-unique values in myField get the highest rank number (I am not sure how to express this since I don't want to say highest rank, which usually implies lower rank numbers!) that any of them would get if that value of myField had been the highest value of myField in the results. This may not be desirable. The alternative to this is the other SQL construct we used for ranking unique rows:

SELECT (SELECT count(*) + 1 from myTable where myField < T.myField) as Rank,
myField from myTable as T ORDER BY myField

In this case, with the same data as in the previous example, you would get the output below:

Rank myField
1 1
2 2
3 3
3 3
5 4
5 4
5 4
8 5

As you can tell, the ranks are not unique and they have gaps. You can be guaranteed that the rank numbers will start from 1. But you can not be guaranteed that the rank numbers will go up to the number of rows in the result set. In fact, if there were 8 rows in the results and all of them had the exact same value in myField, this query would assign a rank of 1 to each row.

Because of the < comparison in the subquery, non-unique values in myField get the lowest rank number (I am not sure how to express this since I don't want to say lowest rank, which usually implies higher rank numbers!) that any of them would get if that value of myField had been the highest value of myField in the results. This may not be desirable either.

So, is there a way to generate unique ranks that run from 1 to the number of rows when the rows are not unique? Well, it turns out this is possible under most circumstances. Consider what the database table is there for: it is there for storing non-redundant data. There really is no reason to store exactly identical data twice or thrice in a table. There must be some field in a table that distinguishes between rows that look identical otherwise. This field could be a timestamp, an autonumber field or anything else.

As long as the table does not actually contain rows that are identical (and this will not be possible in a table with a primary key, whether it is a single-field simple primary key or multi-field compound primary key), there is way to rank rows uniquely in a query based on that table. If you suspect that there may be a case where multiple rows in your table might contain the same data (for instance, the table stores weather observations or observations from some kind of experiment), you should either add a timestamp to each row that will make them unique, or take Access's suggestion during table design and add an autonumber primary key to the table.

Assuming that your table does actually contain a field that never repeats across rows (an autonumber field or a timestamp that is guaranteed to be different for each row in the table), you can rank rows in a query based on that table. The main thing to remember is that ordering can be done on fields in a query that are not displayed to the users. So, suppose myField1 in your table contains non-unique data. Also suppose that myField2 contains unique data (or the combination of myField1 and myField2 is unique across rows). But you want to show only myField1 to the user in your query. The query below will enable you to do so without any problems:

SELECT
(SELECT count(*) from myTable WHERE
((myField1 < T.myField1) OR
((myField1 = T.myField1) AND (myField2 <= T.myField2)))) as Rank,
myField1 from myTable ORDER BY myField1, myField2

Notice that myField2 is not included in the list of fields in the SELECT clause of the main query, but is used in the ORDER BY clause, and also inside the subquery. You can refer to fields in the table in the subquery even if those fields are not actually in the SELECT clause of the main query. And, if it takes more fields to make each row unique, then the subquery can get messy as we saw before.

The output of the query does not make it clear to the user how the ranking was generated because all the fields that were used to order the rows in the query are not visible to the user. This can cause confusion to the user. Keep this in mind when using constructs like the above.

Hope this lesson has been helpful in solving some problem you might have had with ranking rows in a query. If you have any problems or concerns with the queries in this lesson, please feel free to let me know by posting a comment. Good luck!

Wednesday, November 25, 2009

Vedic Mathematics Lesson 28: Solving Equations 3

In the previous lesson, we dealt with various meanings of the term Samuccaya, and how the different meanings of the term help us solve different types of equations using the Sunyam Samyasamuccaye sutra.

You can find all the previous posts about Vedic Mathematics below:

Introduction to Vedic Mathematics
A Spectacular Illustration of Vedic Mathematics
10's Complements
Multiplication Part 1
Multiplication Part 2
Multiplication Part 3
Multiplication Part 4
Multiplication Part 5
Multiplication Special Case 1
Multiplication Special Case 2

Multiplication Special Case 3
Vertically And Crosswise I
Vertically And Crosswise II
Squaring, Cubing, Etc.
Subtraction
Division By The Nikhilam Method I
Division By The Nikhilam Method II
Division By The Nikhilam Method III
Division By The Paravartya Method
Digital Roots
Straight Division I
Straight Division II
Vinculums
Divisibility Rules
Simple Osculation
Multiplex Osculation
Solving Equations 1
Solving Equations 2

In this lesson, we will concentrate on one meaning of the term that is very powerful. We will devote this entire lesson to this meaning since there are several variations of this meaning that can take time to identify correctly for application of this sutra.

The basic equation we will start with has the form 1/(x - a) + 1/(x - b) = 1/(x - c) + 1/(x - d). As you can tell, this does not seem to be an easy type of equation to deal with. In fact, at first glance, it looks as if it is likely to become a cubic equation. Actually, if you take the LCM of the denominators, add up the terms on either side, and simplify the equation, you will get an equation of this sort:

[2x - (a + b)][x^2 - (c + d)x + cd] = [2x - (c + d)][x^2 - (a + b)x + ab]

As you can tell, the x^3 terms on the left and right hand side will cancel out, leaving you with a quadratic equation at best, not a cubic equation. But, the prospect of expanding out the terms further, collecting them to create a standard quadratic equation, and then applying the quadratic formula on it to get the final solution is daunting at best.

The meaning of Samuccaya that we are dealing with in this lesson can make problems like this much simpler under some special circumstances. The special circumstance, in this case, happens to be as follows:

If the sum of the denominators on either side of the equation is the same, that is the Samuccaya. Set it equal to zero to solve the equation. Thus, if 2x - (a + b) = 2x - (c + d), then x = (a + b)/2 (or equivalently, x = (c + d)/2) is the solution. That is all there is to it!

Let us see this meaning of Samuccaya in action with a couple of examples. First, let us consider the equation 1/(x + 2) + 1/(x + 3) = 1/(x + 1) + 1/(x + 4). The sum of the denominators on the left hand side is 2x + 5. We find that the sum of the denominators on the right hand side is also 2x + 5. The sutra then says that 2x + 5 = 0, giving us the solution x = -2.5.

Now consider the equation 1/(x - 9) + 1/(x - 7) = 1/(x - 5) + 1/(x - 11). Once again, the sum of denominators on the left hand side is 2x - 16, which is the same as the sum of the denominators on the right hand side. Thus, x = 8.

Note that the numerator need not be 1 in all the terms above. It can be any constant without affecting the outcome. Thus, it is easy to verify that the solution to 3/(x + 2) + 3/(x - 7) = 3/(x - 1) + 3/(x - 4) is also x = 2.5. That is obviously because the equation could be divided by the constant throughout to make all the numerators equal to 1. In fact, the numerators can be any quantity (known or unknown). The sutra is applicable as long as each term has the same numerator.

The reason this meaning can be hard to apply is because there are many ways in which equations can morph themselves so that it is not readily obvious that this meaning is applicable. So, we will now consider some variations of this type of equation that one may encounter. Some of these variations are hard to spot, but once we do, their solution using this meaning of Samuccaya is practically instantaneous. That not only makes this meaning of Samuccaya very powerful, it also makes it very advantageous for us to spot these variations so that we can convert the equations to standard form and get their solution quickly.

The first variation is quite easy to spot. Consider the equation 1/(x - 4) - 1/(x - 3) = 1/(x - 6) - 1/(x - 5). We only have to realize that two of the terms have been transposed to the opposite sides of the equation for us to change the equation back to the standard form that can be solved by the application of this sutra. Thus, we rewrite the equation as 1/(x - 4) + 1/(x - 5) = 1/(x - 6) + 1/(x - 3). We then see that the sum of the denominators on both sides of the equation is 2x - 9, and therefore x = 4.5.

Thus, the first variation of the standard form of the equation simply involves transposition of the terms. The transposition could involve just one term from each side being moved t0 the other side, or it could be more radical. Consider, for instance the equation below:

1/(x - 3) - 1/(5 - x) = 1/(x - 6) + 1/(x - 2)

In this case, we have to realize that -1/(5 - x) = 1/(x - 5). Thus rewriting that term gives us 1/(x - 3) + 1/(x - 5) = 1/(x - 6) + 1/(x - 2), giving us the solution x = 4.

Thus, the first step in identifying equations to which the sutra is applicable is to make sure that every term is of the form 1/(x + a), and the terms are added to each other, not subtracted from each other (note that I have written the term as 1/(x + a), but a could be negative without affecting the general form into which we are trying to eventually get the terms. Thus the more general way to write the step would be to make sure that every term is either of the form 1/(x + a) or 1/(x - b), where a and b are positive numbers). Once again, if there is some other quantity in the numerator instead of 1, it does not make a difference to the eventual outcome, as long as the numerators are all the same.

Now, consider the equation below:

(x + 2)/(x + 4) + (x + 3)/(x + 5) = (x + 1)/(x + 3) + (x + 4)/(x + 6)

The equation can be rewritten as:

(x + 4 - 2)/(x + 4) + (x + 5 - 2)/(x + 5) = (x + 3 - 2)/(x + 3) + (x + 6 - 2)/(x + 6)

This can then be written as:

(x + 4)/(x + 4) - 2/(x + 4) + (x + 5)/(x + 5) - 2/(x + 5) = (x + 3)/(x + 3) - 2/(x + 3) + (x + 6)/(x + 6) - 2/(x + 6)

Notice that we now have two terms on each side of the equation that simplify to 1. These can be cancelled out, leaving us with:

-2/(x + 4) - 2/(x + 5) = -2/(x + 3) - 2/(x + 6)

Dividing throughout by -2 gives us a standard form of the equation which then leads to the solution x = -4.5.

How do we identify this variation on sight though? The actual transformation of the given equation to the standard form seems somewhat contrived, and we may not want to embark on a series of such transformations without any assurance that the final outcome is going to be the standard form.

There are actually three tests that we can perform to make sure that the equation can in fact be transformed to the standard form. If the equation passes these tests, we can be assured that the equation can indeed be transformed to the standard form.

The first test is performed as follows: Let the given equation be:

(ax + b)/(cx + d) + (ex + f)/(gx + h) = (jx + k)/(mx + n) + (px + q)/(rx + s)

If a/c + e/g = j/m + p/r, then the equation passes the first test. In other words, if the sum of the ratios of the coefficients of the unknown in the numerator and denominator of each term on the left is equal to the sum of the ratios of the coefficients of the unknown in the numerator and denominator of each term on the right, then the equation passes the first test. That is all there is to it! We can then move on and perform the second test.

The second test is performed as follows: Take the same given equation as before. If |b -d(a/c)| = |f - h(e/g)| = |k - n(j/m)| = |q - s(p/r)|, then the equation passes the second test also.

For the third test, look at the signs of each of the four calculated values in the second test without taking their absolute values: b - d(a/c), f - h(e/g), k - n(j/m) and q - s(p/r). If all four of them have the same sign, the equation passes the third test. If there is one negative and one positive term on each side of the equation, then also, the equation passes the third test.

If the equation passes all three tests, rewrite the equation above as below:

(b - d(a/c))/(cx + d) + (f - h(e/g))/(gx + h) = (k - n(j/m))/(mx + n) + (q - s(p/r))/(rx + s)

The resulting equation will either be in standard form or will require a simple transposition of terms from one side of the equation to the other to produce the standard form (the latter occurs if there is one positive and one negative term on each side when performing the third test).

After the equation is brought to standard form, check to make sure that the sum of the denominators is the same on both sides of the equation. If it is, the sutra is applicable for solving the equation. Then, it should be easy to apply the sutra to get the final solution.

Essentially, all we are doing is transforming each term in the given equation as below:

(ax + b)/(cx + d) is rewritten as:

[(a/c)(cx + d) + b - d(a/c)]/(cx + d)

You can expand out the terms to convince yourself that the two forms of the first terms are indeed equivalent.

We then transform the term further as below by expanding the numerator into two terms as below:

(a/c)(cx + d)/(cx + d) + (b -d(a/c))/(cx + d)

This then leaves us with (a/c) + (b - d(a/c))/(cx + d) because the (cx + d)'s in the numerator and denominator of the first term cancel out, leaving us the constant a/c as the first term. When we do a similar transformation of all the terms in the given equation, we get a/c and e/g on the left hand side, and j/m and p/r on the right hand side as constants.

The first test tells us that a/c + e/g = j/m + p/r. So, the constant terms on both sides of the equation cancel out, leaving us with the transformed equation mentioned after the third test.

Because of the second test, we now know that all the numerators have the same absolute value. Because of the third test, we know that either all four of them have the same sign (in which case, the equation is in standard form), or there are terms of opposite signs on each side of the equation (in which case, we need to transpose the negative terms to the opposite sides of the equation to get the standard form).

Let us work through this with a couple of examples so that we can be sure we know how to apply the tests and do the transformations.

Take the equation (2x + 7)/(x + 5) + (4x + 5)/(x + 2) = (3x + 9)/(x + 4) + (3x + 6)/(x + 3) as an example. We perform the first test and verify that 2/1 + 4/1 = 3/1 + 3/1. So, the equation passes the first test.

We perform the second test and verify that |7 - (2/1)*5| = |5 - (4/1)*2| = |9 - (3/1)*4| = |6 - (3/1)*3|. Thus the second test is also passed.

We notice that all four of the terms above, before we take their absolute value, are of the same sign (they are all equal to -3). This tells us that the equation passes the third test also. We now rewrite the equation in its transformed form as below:

-3/(x + 5) - 3/(x + 2) = -3/(x + 4) - 3/(x + 3)

We now verify that the sum of the denominators on both sides of the equation is 2x + 7, therefore, the sutra is applicable. We set 2x + 7 = 0, giving us the solution x = -3.5.

Consider the next equation as below:

(2x + 3)/(x + 3) + (x + 4)/(2x + 2) = (3x)/(2x - 2) + (x - 4)/(x - 1)

Applying the first test, we find that 2/1 + 1/2 = 3/2 + 1/1. So, the equation passes the first test.

Now, apply the second test. We can verify that |3 - (2/1)*3| = |4 - (1/2)*2| = |0 - (3/2)*-2| = |-4 - (1/1)*(-1)|. So, the equation passes the second test.

We also notice that the terms in the second test before taking their absolute values are -3, 3, 3 and -3. Since there is one positive and one negative term on each side of the equation, it passes the third test. So, we rewrite the equation in its transformed state as below:

-3/(x + 3) + 3/(2x + 2) = 3/(2x -2) - 3/(x - 1)

Transposing the negative terms to the opposite sides of the equation gives us an equation in standard form:

3/(2x + 2) + 3/(x - 1) = 3/(2x - 2) + 3/(x + 3)

We now see that the sum of the denominators on both sides of this equation is 3x + 1. Therefore the sutra tells us that the solution to this equation (and the original equation from which this is derived) is x = -1/3.

Obviously, the multitude of ways in which equations can be transformed so that it becomes difficult to identify whether this sutra applies or not, makes it hard to use this meaning of samuccaya in many circumstances. But by being able to perform the tests listed in this lesson mentally and on sight, one can identify equations that are susceptible to this quick and easy solution technique. That can result in large savings of time and effort in actually solving such equations.

Therefore, I hope you will take the time to practice performing these tests on various equations so that you can do them quickly and efficiently. Good luck, and happy computing!

Tuesday, November 24, 2009

Some Intentionally Funny Street Signs

Here are some street signs that highlight the designers' senses of humor! More signs like these would make long road trips a lot more bearable. Enjoy!!

This Sign Has Sharp Edges
Dear Crossing
Old Horses, Blind Horses, Unruly Kids
Parking Spot Reserved For Drunk Drivers
Stop And Smell The Roses Sign
Emergency Stopping Only, Not For Whale Watching Sign
Slow Down Or Die Sign
School Speed Limit Between Precise Times
14 and a half MPH Speed Limit Sign
Elephants Should Cross Two At A Time Sign

Twists And Turns, Just Kidding Sign
Donkey Crossing $10 per Donkey Sign
Big Foot Crossing Precautionary Sign
Our Squirrels Can't Tell One Kind Of Nut From Another Sign

Monday, November 23, 2009

How To Create A Hidden FlagCounter Widget To Keep Your Traffic Stats Private

In this earlier post, I talked about a potentially very useful widget for your blog created by FlagCounter. The widget enables you to showcase the geographic reach of your blog by showing the countries from which you have had visitors.

My only problem with the widget was that there was no option to hide the actual visit counts or change them to percentages. Your traffic stats were revealed to the whole world for everyone to see. If you don't mind that, then I think the FlagCounter widget is an excellent addition to your blog as is.

But, if you are like me, you want some privacy with respect to the traffic stats for your blog or other website. There is really no option on the FlagCounter website to hide the traffic stats or convert them to percentages. The way the widget works is by creating an image that is hosted on the FlagCounter website. Every time your blog or other site with the widget on it is loaded, the image is updated if necessary (with new country flags added, visit counts incremented, etc.), and then it is displayed on your website. Because the widget is actually visible on your website or blog as a single image, you can't change any aspects of the display once the image has been generated on FlagCounter's side.

But FlagCounter does provide you some options that can be used to modify the visual aspects of the widget so that your traffic stats can be hidden from view. In this post, I will explain the procedure for getting a partially hidden flag counter or partially invisible flag counter. This will enable you to leave the flags visible on your site, but hide the actual traffic stats associated with each country. The result of applying the procedure I am going to describe is visible in the left sidebar of this blog page just below the visitor map provided by Revolver Maps (which is another of the recommended gadgets I talk about in the earlier post).

To get a FlagCounter where the visit stats are not visible, go to the FlagCounter website and start the process of creating your own flag counter. Set the number of flags and columns first. For the label on top of the counters, you can choose any option because it is going to be one of those elements that will be hidden as part of the procedure outlined here.

Uncheck the boxes next to "Show Country Codes" and "Show PageView Count". Removing the country codes makes the columns narrower, so you can fit more columns of flags in the same sidebar width. This is an element that will become invisible, so including it only increases the amount of seemingly unused space between columns of flags in the final widget. That is why I prefer leaving it off as part of this procedure.

The pageview count usually adds an extra row to the height of the widget, but since it is also going to become invisible, there is no point in adding empty space to the widget. So, I leave out that element also.

In the middle you will find three boxes to choose the colors to be used in your flag counter. In this area, choose the same color for background and text colors. This is one of the most important steps, and is the step primarily responsible for making the visitor counts invisible. This step also makes the country codes and pageview counts invisible, which is why I advised leaving them out of the widget.

For my blog, I set the background, text and border colors to all white (FFFFFF). You can choose any colors you want, but if you want the stats to be invisible, you need to choose the exact same color for the text and background. If you have performed the steps above correctly, you will see a preview that looks like the image below (I have chosen 4 columns of flags and selected "All" under "Maximum Flags to Show"):

If this looks like the kind of display you would want on your blog or website, hit the button that says "Get your FlagCounter". But you have completed only one of the two steps required to keep the traffic stats secret. Continue reading on for the next step.

When you hit the button to generate your codes, you will be taken to a page with two boxes of codes. For blogs and other websites, choose what is in the top box. It may look something like what is below (this is sample code that I am using to illustrate a procedure. DO NOT use this sample code in your blog or website. Use only the code you have generated for yourself directly at the FlagCounter website):

<a href="http://s05.flagcounter.com/more/ko9R">
<img src="http://s05.flagcounter.com/count/ko9R/
bg=FFFFFF/txt=FFFFFF/border=FFFFFF/columns=4/maxflags=248/viewers=0/labels=0/"
alt="free counters" border="0" /></a>

Now, log into blogger, and go through the procedure for adding a HTML/Javascript page element to your layout (the procedure for is explained in my earlier post if you need the steps to do this). Give this widget a title (such as "Visitor Countries" or something similar). Paste the code you copied from the FlagCounter website into the Content box.

But don't save it as is. Here is where the second part of the procedure to make your stats secret comes in. If you look at the sample code I have posted above, you will find a URL in the first line of the code next to "href =". When visitors to your website click on your flag counter widget, this is the URL they will be taken to.

This URL then allows your visitor to see how many hits you have gotten from which countries, what your total pageviews are, what the history of pageviews over the past week has been and so on. So, if you leave that href as is, your visitors will be able to click on your widget and get all the stats about your site that you have worked hard to keep secret! That is probably not what you want.

But, you yourself probably want to be able to view such statistics whenever you want to. So, that URL is very important to you. Do not lose it. If you do, you will have to generate a new flag counter widget to be able to see these stats (and the new counter will start from scratch, so you will have no record of any of the visitors to your site from before the new counter was generated). So, copy that URL and add it as a bookmark in your browser. You may also want to note the URL down on paper somewhere so that you will have it even if your computer crashes and burns one fine day.

Now that you have squirreled away the original URL, replace it with something else! Since FlagCounter is free because it is advertiser-supported, they rely on traffic that arrives at their site by clicking on their widget. So, change the URL to www.flagcounter.com so that you do not rob them of the traffic that provides you with the free widget in the first place. The modified code now looks as below:

<a href="http://www.flagcounter.com/">
<img src="http://s05.flagcounter.com/count/ko9R/
bg=FFFFFF/txt=FFFFFF/border=FFFFFF/columns=4/maxflags=248/viewers=0/labels=0/"
alt="free counters" border="0" /></a>

Note that modifying the code in any way is a violation of the terms under which the flag counter service is provided to you. So, if you get greedy and try to steal the traffic that FlagCounter relies on, by pointing the href to some other URL, FlagCounter would be well within its rights to terminate your counter. So, please be considerate, and modify it only to keep your traffic stats private, not to take away the traffic that would reach FlagCounter by clicking on their widget.

Hope you find this "hacked" widget useful. I have been using it for the past couple of days on my blog. In the first 24 hours, I accumulated 24 flags, but progress after that has been significantly slower. Given the content of my blog, it has the potential for several dozen more flags, but I don't think I will ever hit the kinds of numbers some people boast about on the FlagCounter forum.

And that is where the forum may come in handy: it actually has a section that allows members to request flags from other forum members so that you can build up an impressive flag collection on your widget! When you get stuck with your widget missing flags from a few obscure countries, you know where to go to beg for someone from those countries to give your blog or website a visit just for the sake of your widget! Good luck with the widget and your flag collection!!

Sunday, November 22, 2009

A Productive Week

I felt better about this week than some of the past few weeks. Things, both at work and at home, seem to have progressed better than in some of the previous weeks. I don't know whether it is just something I feel for no tangible reason, or whether things really are improving!

At work, we had more meetings to figure out where our model was going to get data from. But we managed to locate an expert in the IT department who has an intimate understanding of some of the data hidden deep within various databases and mainframe systems at work. And this person actually was competent, unlike most of the people from the IT department that I interact with on a daily basis.

So, the two meetings we had with this person turned out to be very productive, and we covered a lot of ground in a very short time. The IT person either had the answers to our questions right away or he was not absolutely sure, but knew who would know for sure. Right after the meeting, he would make inquiries, and before the next meeting, he had the answer we needed. It was a pleasure working with him instead of the lazy weasels we had to put up with most of the rest of the time.

With that part of the project making good progress, we had a chance to revisit in more detail other parts of the project that needed some more attention. One of them had been a requirement that the model we were coming up with be prototyped and proved before we proceeded to a final version.

We had been trying to argue with my manager that the requirement did not really make much sense since the components of the model were well-known mathematical modeling constructs that we know work. If someone thought it needed proving, either that person did not believe in mathematics or did not know enough mathematics to understand what we were talking about anyways.

My manager understood the argument perfectly, but was treading a fine line to get through the office politics surrounding the project. This week we managed to make some progress in convincing him to take a harder line with the powers that be to relax this requirement. If the requirement did not cost us anything, we wouldn't mind producing such a proof of concept on the way to the final product.

The problem was that they wanted the proof of concept in the next 3 months. To produce a proof of concept in 3 months, we would have to cut so many corners that the code we develop for it would be pretty much unusable in the final product. So, we would have to work for 3 months to produce a proof of concept, then start pretty much from scratch and produce a final product in the next 6 or 7 months. If we could skip the proof of concept, we could produce the final product 6 or 7 months from now, cutting out 3 months from the total timeline of the project. It made complete sense to everyone except the people who were obsessed with making life difficult for us whether it made sense or not.

My manager has now promised my team and me an audience in front of our director so that we can plead our case more directly up the chain of command. Our director meets with the IT director who has taken charge of this project, on a pretty regular basis, so if we manage to convince our director, we stand a good chance that she would be able to cut through some of the office politics and get this IT director off our case. The next week will be crucial to all of this, but this week laid the ground work for this in a productive fashion. Small things to be happy about, I guess.

At home, my kids scored pretty high on the first test of the math olympiad. It is a series of 5 tests, spaced 4 weeks apart from each other, so they still have tests going all the way to March. But in the first test, my elder daughter got all the questions right, and my younger daughter missed just one question. They were the first and second highest scorers in their school, so they are off to a good start.

I am teaching them a bunch of different problems involving percentage discounts, interest rates, tax rates, etc. The concepts seem to be very confusing to them initially even though we have been through this a year or so back also. After putting them through the paces once again, they have gained some confidence in tackling these kinds of problems, working back and forth between original prices, sale prices, total prices, original investments, returns, etc.

Also, when a friend of mine visited me during the summer, we had talked casually about how it might be a good time to invest in some real estate given the steep fall in real estate prices in some parts of the country. I had mostly forgotten about it and did not take any further action on it - until this week. My wife convinced me to follow through on that thought, so I called up my friend and asked him to put me in touch with a real estate agent he knew. I don't know if anything will come out of this, especially if the prices (even after their fall) do not fall within my budgetary constraints. I just have to play along and see what happens. But productive on the home front, nevertheless.

I continued posting to my blog with a humorous post picked out from one of my old emails, a post on Access, another on Vedic mathematics and a post on Blogger gadgets/widgets that I find useful on my blog. That post triggered some research into flag counters and trying to make a flag counter invisible so that traffic stats are kept private. I think I may have a solution, but I have to test it out fully before I post it, so watch this space for updates on that issue.

I was also invited to be a guest poster on the Vedic Maths India Forum blog based on the Vedic Mathematics posts on my own blog. So, for the past two weeks, I have been summarizing my Vedic Mathematics posts and posting them on this other blog also. This has given me an opportunity to introduce my blog and its posts to a wider audience.

I mentioned last week that I was having trouble posting to this other blog because Blogger would not give me the option of posting to it even though I had been added as an author on this team blog. The solution turned out to be pretty simple, as I suspected. When the blog owner added me as an author, Google kicked off an email to me with an activation link in it. If I had clicked on that link right away instead of putting the email away for later, I never would have had the problems I subsequently faced trying to convince Blogger that I was authorized to post on this other blog! As soon as I located that email again and clicked on the activation link, I was asked to log in with my Google credentials, and voila, the other blog showed up in my Blogger dashboard, allowing me to post to it.

My Wednesday karate class was quite uneventful. After going through all the basics, I taught the new students some self-defense moves. My sensei brought a padded stick to the class, so I also practiced defense against a club-like weapon (a night-stick, a flash light, tire iron, ball bat, etc.) with the new students. I also learned the next few moves in my kata (seyunchin), which is turning out to be quite an intricate and confusing kata with lots of new types of moves. The class is not going to meet on the day before thanksgiving, so I am going to have a 2-week break before the next class.

Now, I have to take care of the laundry, get ready for dinner, finish watching a movie and get ready for a new week. Thankfully it is going to be a short work-week. I hope it is sweet too!

Saturday, November 21, 2009

Useful Tools And Gadgets For Bloggers

I have been blogging on the Blogger platform for a little over 5 months now. I was completely new to blogging and Blogger when I first started. After experiencing the Blogger platform first-hand for the past several months, and doing several hours of research, I have found several tools and embedded "gadgets" that make your blog more attractive, useful to your visitors and make it easier to keep track of visitors and their tastes, likes and dislikes.

In this post, I want to take some time to introduce some of the more useful tools/services and gadgets I have had the chance to use and benefit from. Before I introduce the gadgets and services I use, I want to provide a brief introduction that will cover the use of gadgets on the Blogger platform.

When you log into Blogger, under "Layout", click on "Pick New Template" to be shown multiple layouts to choose from for your blog. I like the layout I currently use for my blog which has a left side-bar and a main post area that covers the rest of the page. There are several other templates, but in general, all templates have at least one side-bar in addition to the main area of the page that contains blog posts.

In addition to the templates provided by Blogger, you can google for "downloadable blogger templates" to find lots of attractive templates created by many people around the world. Most of them can be used for free, but you may have to pay for some of them. When you download any of them, save it as a file on your computer, then in the Blogger dashboard, click on "Layout" and "Edit Html". In the section titled "Backup/Restore Template", you can upload this newly downloaded template file for use as the new template for your blog.

Assuming your template has a sidebar and a main posts area, you can add gadgets to your blog by clicking on "Layout" and choosing the "Page Elements" tab. How exactly the resulting page will look depends on the blogger template you choose to use. I have attached an image below showing how my page elements are laid out.

To add a gadget, you click on the "Add a Gadget" link in the sidebar (there is an "Add a Gadget" link in the bottom area of the page also. Note that you can add a gadget anywhere, then drag and drop it wherever you need it. So, it does not matter which "Add a Gadget" link you click). The resulting page is shown below:

To add most gadgets to your blogger page, you need to use the HTML/Javascript option as highlighted by the mouse pointer in the image above. When you click on that, you get the screen below:

You can enter any title you want in the Title box, then paste the "code" provided to you by the gadget you are trying to add to the blog in the Content box. Hit Save and the gadget gets added to your blog page as a page element right below the "Add a Gadget" button. You can then drag this page element to anywhere on your page and drop it there. Note that the page layout does not scroll when you drag an element from one location to another, so if you have a lot of elements on your page, you might have to drag the element across multiple pages by moving it one screen at a time.

Now that we know how to add gadgets, let us look at some gadgets that I have found useful.

Google Gadgets: Google makes available a huge number of gadgets, some of them coded up by Google, others coded by independent third parties, for use on your blog. You can find some of them simply by clicking on "Add a Gadget" on the layout page of your blog (click on "More Gadgets" on the left to get a list of over 1000 gadgets you can add to your blog). To add these gadgets in most cases does not actually involve adding a HTML/Javascript element to your page.

Some of the gadgets available here allow you to add buttons for people to start following your blog, subscribe to your blog, show an archive of posts so that visitors can navigate your blog, show post tags, add text and/or image boxes to your sidebars, embed YouTube videos in your blog's sidebars, etc., etc. There are also gadgets that show your visitors a wealth of information about themselves, including their IP address, operating system, browser, etc.

Another source of a huge number of gadgets is the Gadgets Powered by Google website. Unfortunately, this site lacks decent search and ranking capabilities, so it can be a bit of a pain finding what you want, but this can be a treasure trove of good-looking gadgets that add elements of usefulness and/or fun to your blog. This page gives you the general procedure for adding any of these gadgets to your blog. This page lists over 179,000 gadgets available for your use, but you have to use the search box to narrow down the list because you can't go past the first 1,000 gadgets if you want to browse through all of them. Yes, it is some kind of Google limitation that does not make much sense especially since you can not change the order in which the gadgets are displayed by using any ranking criteria.

StatCounter: StatCounter is the absolute best website analytics tool I have found on the web that is also completely free. There are paid versions of the tool available, but I have found the details provided by the free tool more than adequate. Once you register your account with StatCounter, you can choose what type of counter you want on your web page, and generate the appropriate code. You then add a HTML/Javascript gadget to your blog, and copy and paste the code into the content of this gadget. Your blog is now "wired" and starts collecting statistics for your analysis.

You can visit the features page at StatCounter for full details on the kinds of statistics the site makes available to you for analysis. The information is rich and detailed, and can help you make several decisions about your blog. And it is all in real-time! The summary page tracks how your site is doing in attracting traffic. The Recent Came From and Came From pages show you where your traffic is coming from. If you are getting traffic from search engines, the Recent Keyword Activity, Keyword Activity and Search Engine Wars pages will provide you tips on how to optimize your use of keywords. The Popular Pages page is very useful in deciding what types of posts are attracting more of your visitors' attention.

One of the most fascinating pages is the Recent Visitor Map page. You can not only see the geographic reach of your site, you can also click on any of the geomarkers to get detailed information on that particular visitor, including how many times and at what times the visitor came to your site etc.
The site also provides you statistics that enable you to figure out what kinds of computers and browsers visitors are using when they visit your site, how much time they spend on your site, what exit links they follow, what they download from your site, etc., etc. As I said, this site is one of my absolute favorites, and I can't do it justice in words. You have to sign up and experience it to appreciate it!

The site has detailed help on each of the features and a reasonably active forum where members discuss various topics related to visitor tracking using StatCounter. If you have managed without StatCounter for a while, you will wonder how you did it after you get used to it for a few days!

Google Search: This is a gadget that provides a convenient search box for your visitors to search the entire web or just the contents of your blog. You can find the details of how to add the gadget to your website at this link. If you have a Google adSense account, you can also set it up so that Google serves search ads as part of the search results everytime your visitors use your search box. This can provide you additional advertising income.

Google Translate: You can embed a Google Translate gadget on your blog so that visitors to your blog can read it in any one of more than 50 languages that Google Translate can translate to and from. To embed the translator into your blog, go through the steps to add a HTML/Javascript gadget to your blog, give it a title, then paste the following code into the content box:

<div id="google_translate_element"></div><script>
function googleTranslateElementInit() {
new google.translate.TranslateElement({
pageLanguage: 'en'
}, 'google_translate_element');
}
</script>
<script src="http://translate.google.com/translate_a/element.js?cb=googleTranslateElementInit">
</script>

After you do that, you will see a small gadget in your sidebar with a dropdown box that lists various languages. When your user chooses one of these languages, your posts are translated on the fly into the language of your visitor's choice and shows up on the same page seamlessly. You can try this out by translating my blog into any language of your choice by using this gadget. The Google Translate gadget is located at the top of the left sidebar on this page. This can not only increase visits to your blog by visitors whose primary language is not the language you write in, it can also help you in teaching yourself foreign languages if you are interested!

Google News: You can see a sample of this gadget above the posts in the main area of my blog page. I find it useful not only to keep track of news, but also provide something of value to my visitors so that they can read the day's news while reading my blog at the same time. The articles rotate at fixed intervals, and the subject area also rotates, giving the viewer a broad array of relevant and up-to-date news. To create this gadget, go through the steps to add a HTML/Javascript gadget to your blog, give it a title, then paste the following code into the content box:

<iframe frameborder="0" width="728" marginheight="0"
src="http://www.google.com/uds/modules/elements/newsshow/iframe.html?topic=h,w,b,n,t,el,p,e,s,m&rsz=large&format=728x90"
height="90" marginwidth="0">
</iframe>

This Day In History: This is a free gadget provided by The Free Dictionary to spice up your blog and provide some extra value to your readers, who may learn something new from the information provided from history in this gadget. The gadget is more static than I would like it to be (the content never changes throughout the day even though there may have been multiple interesting things that happened on that date in years past), but it is better than nothing. Ideally, I would have preferred a gadget in which various historic events in different areas like science, technology, sports, etc., and different years, are rotated throughout the day, but until I find a gadget like that, I will stick with this one! You can see a sample of this gadget below all the posts in the main area of my blog page.

Spottt and BlogUpp: These are publicity tools you can use to generate visibility for your blog. These two gadgets give you the ability to publicize others' blogs on your blog, and in return, your blog gets visibility in others' blogs. I have talked in more detail about these two gadgets in my earlier post about traffic exchanges.

Feedjit Widgets: Feedjit provides you with embeddable widgets that can provide you and your visitors with lots of information. The free widgets can be found here, and include a free traffic feed (to help you understand where your traffic is coming from), a free traffic map (to show the geographic locations of your visitors), page ranking by popularity, and recommended reading. The last widget looks at the behavior of past visitors to this page and recommends other pages on the site that these visitors went to, as guidance for later visitors.

I have found that these widgets are not very visually appealing, but a couple of them (the page popularity rankings and recommended reading) are somewhat useful. Unfortunately, the traffic map is not resizeable, and gets cluttered very quickly, so I would not recommend the visitor map from this website. Continue reading on for a much better choice that I personally use and recommend!

Revolver Maps: Revolver Maps produces real-time visitor maps that are visually pleasing. There are two main types of visitor maps you can get from Revolver. One of them is a rotating globe with visitors marked with dots, the other is a plain 2D map with visitor locations marked on it. Both maps are resizeable, so you can size them to fit the size of your sidebar. The maps themselves can also be colored in several different ways, and can have seasonal color variations imposed on them. The visitors locations "glow" adding to the visual appeal of these maps. All in all, I find this map to be an excellent visual element that adds a touch of class to any web page.

FlagCounter: If you have ever wanted to know exactly how many visitors visit your blog from each country of the world, then FlagCounter makes just the ideal widget for you. They make a highly customizable widget that shows you a list of countries your visitors came from along with the country flag, and optionally, the two letter internet code of the country. Next to each flag is the number of visitors from that country. Unfortunately, I have not found a way to just show the flags without the visitor numbers being revealed (or even show the visitor numbers as percentages), and I prefer to keep my traffic stats to myself, so I do not use this widget, but I can see this widget being very useful to some other bloggers. The FlagCounter site also has a support forum to get any questions you may have answered.ShareThis: ShareThis enables you to add an icon to the bottom of each post that enables your visitors to submit a link to that post to networking sites like Reddit, Digg, Twitter, Facebook, etc. They can also use the button to bookmark the link in their Del.icio.us account, Google Bookmarks, etc. This is a very useful gadget since it enables your visitors to publicize your content to a wider audience.

Outbrain: This is a small gadget that also goes below each blog post, and enables your visitors to rate each post using 1 to 5-star system. The gadget also recommends further reading options to your visitors in the form of links below each post. The gadget is unobtrusive, and blends well with your blogger template so that it does not stand out like a sore thumb even on busy backgrounds.

There are literally millions of other widgets and gadgets that are being produced on a daily basis by various people around the world. What I consider useful is not necessarily what you might consider useful, and my tastes may not match yours either. So, feel free to do your own research by scouring other blogs for interesting gadgets as well as doing google searches for interesting blogging gadgets. Some other sites specializing in widgets and gadgets are WidgetBox, Wowzio, SpringWidgets, YourMinis, Widgipedia and Widgiland.

Hope you found this post useful in customizing your Blogger page. Gadgets have the ability to provide you a lot of information about your visitors while also helping your visitors navigate your blog, rate its content, provide you feedback, etc. Make sure you take full advantage of various gadgets in marketing and improving your blog. If you use and find a lot of value in gadgets I have not mentioned in this post, please feel free to comment on this post with information so that I can try them, and include them in a later post.

If you look at the sidebar of my blog, you will also notice a large number of banners provided by various non-profit organizations like the World Wildlife Fund, the World Food Program, The American Red Cross, etc. These organizations are worthy of all the publicity (and possibly, donations) they can get, so please consider visiting their sites, and getting the code for their banners and flash widgets, so that you can provide them publicity and visibility through your blog. Note that if you click on any of these banners, you will be taken directly to the organization's website, so you do not have to play any role in collecting or conveying donations to these organizations through your blog.

Before I let you go, there is just one more gadget I want to talk about:

Dilbert: Like a true geek at heart, I love Dilbert cartoons. The Dilbert widget enables you to embed the Dilbert cartoon right in your blog's sidebar so that you don't have to leave your blog to read each day's Dilbert! The gadget is flexible enough to enable you to scroll backwards and forwards through multiple days or weeks. The site gives you several sizes to choose from, and hopefully one of them will fit well on your blog template.

Visitors Country Map

Free counters!

Content From TheFreeDictionary.com

In the News

Article of the Day

This Day in History

Today's Birthday

Quote of the Day

Word of the Day

Match Up
Match each word in the left column with its synonym on the right. When finished, click Answer to see the results. Good luck!

 

Hangman

Spelling Bee
difficulty level:
score: -
please wait...
 
spell the word:

Search The Web