thenez101 - a blog about nothing and everything

Monday, January 27, 2014

Getting the top row for a group of records (T-SQL)

From time to time I find my self in need for writing queries that show top record for any group of records. Here is a simple method for grouping and displaying the desired output.

Let's say that our table consist of the following recordset:

Name Price Product
-------- ------- ----------
Joe 5 Car
Mike 8 Shoes
John 12 Theatre ticket
Joe 15 Theatre ticket
Joe 32 Shoes
Mike 7 Boat

Now, let's see how we can get every guy's most expensive purchase.

;with cte as (
select row_number() over (partition by Name order by Price desc) as Row, Name, Price, Product
from purchases
)
select Name, Price, Product from cte where Row = 1;

This will give us the desired response, i.e. a recordset that consist only the most expensive purchases for each guy

Name Price Product
-------- ------- ----------
Mike 8 Shoes
John 12 Theatre ticket
Joe 32 Shoes

And now for a short explanation:

The row_number() adds a row number for each row (surprise surprise...). The real deal here is the partitioning of the content. Here we partition the content (or group in other words) by the Name and order it by the price descending. So in fact what we get is the following recordset:

Row Name Price Product
------ -------- ------- ----------
1 Joe 32 Shoes
2 Joe 15 Theatre ticket
3 Joe 5 Car
1 Mike 8 Shoes
2 Mike 7 Boat

1 John 12 Theatre ticket

So what we need now is to select only the first row for each group and that's that.

Wednesday, November 2, 2011

3 months later…. predictions are following as planned

3 months passed and the trends continued as predicted. Firefox continues to drop, as well as IE while Chrome is growing rapidly.

let’s see what the future will give us.

Darts–not only a game

Google decided to have its own programing language. If you’ll think about it for a moment why not. Apple has one, Microsoft has more than one, Sun (a.k.a Oracle) has one. So now, they have one. Its name is Dart (just like the game. They say that its goal is to solve every JavaScript issues that may occur in large scale web application.

If you think on trying it, you may want to check http://www.dartlang.org.

When you go there, you might want to download the Dartboard (cool name) which is the Dart Editor and read (I read only some…) of the technical spec.

I can tell you from my point of view that this is really cool and exciting. Still lots to do……

Oh, and the Fibonacci Sunflower sample is amazing (and no…. it does not work on IE, since they do not support HTML5 yet)

Wednesday, July 20, 2011

Google Visualization BarChart Paging

Here is a very simple sample for paginating bar chart I created. I’m using it for a very large scale data table (over 1000 rows and it works like charm)

Create a data table object with the relevant data
Create a pagination object that will hold the total number of pages, current page and page size
Create two buttons that will change the page number
Create a population function that will fill the bar chart

<html>
<head>
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load("visualization", "1", {packages:["corechart"]});
      google.setOnLoadCallback(InitChart);
      var data = {}, chart;
      var pager = { currentPage: 0, countPages: 0, pageSize: 10 };
      var options = { width: 400, height: 240, title: 'Company Performance',
                            vAxis: { title: 'Year', titleTextStyle: { color: 'red'}} };

      function InitChart() {
        chart = new google.visualization.BarChart(document.getElementById('visualization'));
        data = new google.visualization.DataTable();
        data.addColumn('string', 'Year');
        data.addColumn('number', 'Sales');
        data.addRows([
          ['1990', 31], ['1991', 2345], ['1992', 521], ['1993', 429], ['1994', 213], ['1995', 2102],
          ['1996', 802], ['1997', 910], ['1998', 3112], ['1999', 1030], ['2000', 1000], ['2001', 1170],
          ['2002', 660], ['2003', 501], ['2004', 2121], ['2005', 845], ['2006', 1030], ['2007', 1095],
          ['2008', 330], ['2009', 10], ['2010', 506], ['2011', 1120]
        ]);

        // setting number of pages and moving to first page
        pager.countPages = Math.ceil(data.getNumberOfRows() / pager.pageSize);
        Paginate('next');
    }

    function Paginate(dir) {
        // moving page number
        if (pager.currentPage == 1 && dir == 'prev') return;
        if (pager.currentPage == pager.countPages && dir == 'next') return;
        if (dir == 'next') pager.currentPage += 1;
        if (dir == 'prev') pager.currentPage -= 1;

        var cloneTable = data.clone();
        var numOfRows = cloneTable.getNumberOfRows();
        // validate existance of data for pager, and remove rows
        if (numOfRows > 0 && pager.currentPage <= pager.countPages) {
            // remove previous rows
            cloneTable.removeRows(0, (pager.currentPage - 1) * pager.pageSize);
            // remove next rows
            cloneTable.removeRows(pager.pageSize, cloneTable.getNumberOfRows() - pager.pageSize);
        }

        // filling empty values for rest of last page
        numOfRows = cloneTable.getNumberOfRows();
        if (numOfRows < pager.pageSize)
        {
            for (var i = numOfRows; i < pager.pageSize; i++) {
                cloneTable.addRow(['', 0]);
            }
        }

        chart.draw(cloneTable, options);
    }
    </script>
</head>
<body>
    <div id="visualization"></div>
    <span style="cursor:pointer;" onclick="Paginate('prev');">Prev</span> 
    <span style="cursor:pointer;" onclick="Paginate('next');">Next</span>
</body>
</html>

I hope you might use it. I know I am :-)

Tuesday, July 19, 2011

Google Visualization API Charts

If you’re a newbie to the Google Visualization API (a.k.a. Google Charts) as I was a few months ago you might find that you need some conceptual things to understand. Here is a small list that you might find useful.

Know and understand the basics of the JavaScript language.
JavaScript is the language in which we generate the graphs. In order to know how to use the API, you must know the basics of this language.
Know what is JSON (json.org)
JSON (JavaScript Object Notation) is the way we define objects and their properties. Here we use this to define properties.
References and Help!!!!
You can get all the help you need at the Google Visualization API website at: http://code.google.com/apis/chart/
All other information you might need you can find on the “Google Visualization API group” at: http://groups.google.com/group/google-visualization-api

Creating you first chart

First of all create a reference to the Google Visualization API JavaScript library by adding this line to your HTML header: <script type="text/javascript" src="https://www.google.com/jsapi"></script>
After loading this library, you can access the “google” object (pay attention that this object is all lower case). You can load packages, generate object instances etc.

The simplest way to describe it is by example. Here is a simple 3D Pie Chart (as taken from the API samples)

<html>
<head>
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load("visualization", "1", {packages:["corechart"]});
      google.setOnLoadCallback(drawChart);
      function drawChart() {
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'Task');
        data.addColumn('number', 'Hours per Day');
        data.addRows(5);
        data.setValue(0, 0, 'Work');
        data.setValue(0, 1, 11);
        data.setValue(1, 0, 'Eat');
        data.setValue(1, 1, 2);
        data.setValue(2, 0, 'Commute');
        data.setValue(2, 1, 2);
        data.setValue(3, 0, 'Watch TV');
        data.setValue(3, 1, 2);
        data.setValue(4, 0, 'Sleep');
        data.setValue(4, 1, 7);
        var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
        chart.draw(data, {
                             width: 450,
                             height: 300,
                             is3D: true,
                             title: 'My Daily Activities'
        });
      }
    </script>
</head>
<body>
    <div id="chart_div"></div>
</body>
</html>

What are packages
Packages are, as you might think, an array of packages you intend to use, and thus request to load into the browser memory. The most common package is the “corechart”, others are: “table”, “geomap”, “geochart”, “annotatedtimeline”, “motionchart”, “orgchart”, “treemap” and “charteditor”

The Google DataTable and DataView
The data table object is a holder of data in the form of table (similar to any table format you can think of). It has columns and rows. You can create this object in several ways.

Creating an empty data table and filling it. Similar to the previous example

var data = new google.visualization.DataTable();
data.addColumn('string', 'Task');
data.addColumn('number', 'Hours per Day');
data.addRows([
['Work', 11],
['Eat', 2],
['Commute', 2],
['Watch TV', 2],
['Sleep', {v:7, f:'7.000'}]
]);

In this sample we create a table with 2 columns and populating it using the “addRows” method. In the previous example we first allocated 5 rows and then set their values.

Creating a datable and populating it during the creation stage (in the constructor)

var dt = new google.visualization.DataTable(
     {
       cols: [{id: 'task', label: 'Task', type: 'string'},
                {id: 'hours', label: 'Hours per Day', type: 'number'}],
       rows: [{c:[{v: 'Work'}, {v: 11}]},
              {c:[{v: 'Eat'}, {v: 2}]},
              {c:[{v: 'Commute'}, {v: 2}]},
              {c:[{v: 'Watch TV'}, {v:2}]},
              {c:[{v: 'Sleep'}, {v:7, f:'7.000'}]}
             ]
     },
   0.6
);

Methods, events, listeners and triggers
All of the mentioned above are supported by this API. You can listen to events, such as “select”, “onmouseover” etc. Each graph element has a set of methods and events that you can use for your application purposes.

var table = new google.visualization.Table(document.getElementById('table_div'));
table.draw(data, options);
google.visualization.events.addListener(table, 'select', selectHandler);

function selectHandler() {
alert('A table row was selected');
}

In this example we created a table using the Table control, populated it and attached a “select” listener to its rows. Whenever a row was selected (clicked) the “selectHandler” function was called.
Similar to this, you can catch mouse movements, maps region selection (“regionClick” event in the GeoChart), pie slices selections, column bar selections and much more.

The “Playground”
Every graph you want to try before you generate your own code you can try on the “API Playground” at: http://code.google.com/apis/ajax/playground/?type=visualization
Here you can select every graph type you desire, modify and debug them.
Limitations:
Although it’s created by Google, we have limitations for using the graphs. Most of them are due to the fact that all graphs are generated on the user’s browser (the client) and thus limited by the browser type, computer CPU and memory. From my experience, before trying to populate large amount of data and “believing” the API will do the trick, think and generate only what you need.

References:

Thursday, July 14, 2011

Is this the end of IE? Or is it the end of FF?

If you are following the usage trends of the internet browsers, you must have heard by now on Google Chrome and Firefox.

A few years ago, when Microsoft had us all chained to their internet explorer some people in the community decided that enough is enough and created a brand new (some say not so new, but who cares) browser named “Firefox”. Firefox had a slow start, mostly used by the internet “geeks”. But through time, others started to use and enjoy it, and Microsoft, now understanding that neglecting its users wasn’t so smart, started to lose its grip on the internet browsers world. And indeed, in the first half of the year 2009 the trends changed and the “Firefox” was the most common used browser.

In the meantime, a “small” company name Google decided that all the existing browsers lack the same fundamental thing, which is speed. It believed that the future of all computer communication is via browsers, i.e. using “Cloud” services, such as email, documents, banking services etc. For that purpose it started to develop a brand new internet browser with a fast JavaScript engine that it called “Chrome”. Once again, at start, only the “geeks” used it, but slowly, it became more and more popular and started to bite of the Firefox and IE users share.

Today, after almost 3 years in the market, the Google chrome holds around 18% of the users’ usage market, whereas Mozilla Firefox holds around 42% and Microsoft Internet Explorer holds around 23%.

Here is a small chart I created using the w3schools.com website statistics showing the browsers usage for the past 3 year.

Taking these statistics into consideration and generating a trend line for the next 6 months, we can estimate that the Google chrome browser will not only pass IE (which it already did a few months ago), but also pass FF (which is unbelievable). I believe that unless the Firefox people will wake up they, just like Microsoft in the past, will lose their crown to the Google chrome.

I know that all these words are merely speculations, but, hey….. these numbers don’t lie. They are based on real users. On any case, we are surely in the beginning of a wonderful future on the web. With 3 independent companies kicking their buts to make better browsers it can only do good to us all (I hope so anyway)

Sunday, November 14, 2010

JavaScript as an OOP language


What is the definition of an OO programming?

Object-oriented programming (OOP) is a method programming paradigm that uses "objects" – data structures consisting of data fields and methods together with their interactions – to design applications and computer programs. Programming techniques may include features such as data abstraction, encapsulation, modularity, polymorphism, and inheritance (Wikipedia). So what is the connection between all of that and JavaScript?
Well, let's look over, with samples, all of the OOP characteristics and see for ourselves whether or not JavaScript have what it takes.

Objects:
This is plain and simple, JavaScript allows you to create classes and objects. You can have private and public variables and functions within. For example:

var myClass = function() {
   var privateVar = 'this is a private variable';
   this.publicVar = 'this is a public variable';

   function privateFunction() {
      return;
   }

   this.publicFunction = function() {
      return;
   }
}

Data Abstraction:
Similar to Java, JavaScript allows you to create an abstract class and extend it using prototype approach. For example: an abstract class named Animal and its usage with regard to various animals.

var Animal = function(type) {
   var animal;
   switch (type) {
      case "cat": animal = new Cat(); break;
      case "dog": animal = new Dog(); break;
      case "fly": animal = new Fly(); break;
   }

   this.eat = function() {
      animal.eat();
   }

   this.drink = function() {
      animal.drink();
   }

   this.speak = function() {
      animal.speak();
   }
}

var dog = new Animal('dog');
var cat = new Animal('cat');
dog.speak();
cat.eat();

Encapsulation:
JavaScript in its basis is encapsulated. A very simple and elegant way to demonstrate it is by using an anonymous function

(function() {
   var privateVar = 'this is private';
   this.publicVar = 'this is public';
})();

Another example will show a class and its usage

var Class = (function() {
   //Private members
   var privateVar = '';
   function privateFunction() {
      // Do something
   }

   return {
      //Public members
      publicFunction: function() {
         privateFunction();
      }
   }
})();

Class.publicFunction();

Modularity:
A very simple way to explain this is by describing a small problem and its solution.
An AJAX request is needed in an application. A handler dealing with these kinds of requests is a handler that we can call AjaxHandler (module A). A request can sent by XML (SOAP) or by coma separated variables. For handling XML we use another handler that deals with XML and nodes, we can call it XmlHandler (module B).
Both modules are incorporated into the same routine in order to retrieve data from the server, although they are composed separately and can be tested separately.

Polymorphism:
In order to use polymorphism with JavaScript all you have to do is to override the appropriate function. For example:

var Dog = function() {
   this.bark = function() {
      alert("wuff");
   };
}

var dog = new Dog();
dog.bark(); // "wuff"

function Cat() { }
Cat.prototype = new Dog();
Cat.prototype.bark = function() {
   alert("miao");
}

var cat = new Cat();
cat.bark(); // "miao"

Inheritance:
Inheritance in JavaScript can be done in various ways, among which are: prototype chaining, constructor “stealing” and other methods. Here I will show a combination of the two, as presented by Nicholas C. Zakas in “Professional JavaScript® for Web Developers, 2nd Edition, 2009”

function SuperType(name) {
   this.name = name;
   this.colors = ["red", "blue", "green"];
}
SuperType.prototype.sayName = function() {
   alert(this.name);
};

function SubType(name, age) {
   //inherit properties
   SuperType.call(this, name);
   this.age = age;
}

//inherit methods
SubType.prototype = new SuperType();
SubType.prototype.sayAge = function() {
   alert(this.age);
};

var instance1 = new SubType("Nicholas", 29);
instance1.colors.push("black");
alert(instance1.colors); // red,blue,green,black
instance1.sayName();     // Nicholas;
instance1.sayAge();      // 29

var instance2 = new SubType("Greg", 27);
alert(instance2.colors); // red,blue,green
instance2.sayName();     // Greg;
instance2.sayAge();      // 27

thenez101 - a blog about nothing and everything

Monday, January 27, 2014

Getting the top row for a group of records (T-SQL)

Wednesday, November 2, 2011

3 months later…. predictions are following as planned

Darts–not only a game

Wednesday, July 20, 2011

Google Visualization BarChart Paging

Tuesday, July 19, 2011

Google Visualization API Charts

Thursday, July 14, 2011

Is this the end of IE? Or is it the end of FF?

Sunday, November 14, 2010

JavaScript as an OOP language

Blog Archive

About Me

thenez101 - a blog about nothing and everything

Monday, January 27, 2014

Getting the top row for a group of records (T-SQL)

Wednesday, November 2, 2011

3 months later…. predictions are following as planned

Darts–not only a game

Wednesday, July 20, 2011

Google Visualization BarChart Paging

Tuesday, July 19, 2011

Google Visualization API Charts

Thursday, July 14, 2011

Is this the end of IE? Or is it the end of FF?

Sunday, November 14, 2010

JavaScript as an OOP language

Read from anywhere

Blog Archive

About Me