(2010 Update)  Will Strohl has given a different example of creating a "Most-Comented Blogs" listing using the now-updated DNN "REPORTS" module.  Will's example is much more elegant and powerful than the example I presented back in 2008 (shown below) thanks to the REPORTS module's support for XSLT.  Check out Will's solution here!

Another example of using the free AdvancedDataGrid module to instantly create new functionality on your site.  This time we'll create a clickable "Most Commented Blogs" list (just like the one used on this site) to use with the standard DNN Blog module...

Useful by Example
The best way to illustrate just how immenently useful "SQL" modules are is to show real-world examples.  In case you missed Part 1 and Part 2, "SQL" modules allow you to execute your own database query and present the results as a table in a webpage.  This, in effect, allows you to use SQL queries to effectively contruct your own DNN modules.

There are several "SQL" modules availalbe for DNN.  I prefer to use free offerings when possible so I really like the open-sourced "Advanced Datagrid" module and the closed-source-but-more-powerful "SQLView" module. 

Most-Commented Blogs
In this example we'll use the "Advanced DataGrid" module to create the same "Most Commented Blogs" box you see on this page.  The result will be a simple 1-column table with a clickable list of blog entry titles reflecting the five blogs with the most user comments.  

Our example is using the DNN core "BLOG" module -- if you're using a different modlue for blogging then this example will need to be modified to fit you blog module's database schema.  

Setting this up to show data from the DNN BLOG module is a simple two-step process:

(1) Add the "Advanced Datagrid" module to a page.
(2) In the module settings, copy-and-paste the SQL query into the settings.

Here's the SQL query: 
NOTE:  For formatting purposes, all HTML tags are shown with an extra space just 
after the "<" symbol and just before the closing ">" symbol.  You will need to remove 
the extra spaces if you copy-and-paste code directly from this page
 
select Title from
(
select top 5 count(*) as [#],
  '< a href="'+(select ble.PermaLink 
   from blog_entries ble 
   where ble.entryid = blc.entryid)+'">'+
  (select ble.Title 
   from blog_entries ble 
   where ble.entryid = blc.entryid)+'< /a >'
   as [Title]
from blog_comments blc
where blc.approved = 1
group by blc.entryid
order by [#] desc
) as Result1
 
 
As you can see, the query contructs HTML links using the blog entries' "PermaLink" values as the links and the "Title" values as the displayed link text.  This works great since the Advanced Datagrid renders results as HTML rather than just plain text.  If you prefer to display only the "top 3" or as many as "top 10" posts instead of "top 5" simply change the value between "top" and "count" on line 2.

That's it!  Super simple but very effective.  It should be easy to see how you could use the same approach to creating all sorts of lists:  most-active users, most-visited pages, most recent login failures (a good admin tool), etc.