<< Reminder: Geek Dinner | Home | .Text Stats: Top 10 Agg Views >>

.Text Stats: Top 10 Web Views

This is the first in a series of posts about getting some statistics for a .Text blog. I've been using the SQL below (and the SQL to come in subsequent posts) for several months now. When I first wrote this stuff, my original intent was to turn these into custom controls to plug into .Text and share the controls with everyone. However, I've never been able to set aside time to do that, and I won't for the foreseeable future. So I figured I should at least post the SQL statements that way anyone who has more time than me can maybe create the controls.

Some caveats to running these SQL statements. These were written for version 0.95.2004.102 of .Text and hasn't been tested with any other versions. And in order to actually run the SQL, you must have access to the database with all the .Text stored procedures (now you know why I wanted to turn these into controls - so everyone could benefit).

Without further ado, here's the SQL statement for getting the top 10 blog posts by number of web views. Just replace the value of the UserName to match your blog's virtual directory.

DECLARE @blogID int

/* Get blog ID */
SELECT @blogID = BlogID
FROM blog_Config
WHERE UserName = 'dave'  /* Replace value here */

SELECT TOP 10
    a.EntryID,
    a.WebCount,
    b.Title
FROM blog_EntryViewCount a JOIN blog_Content b
ON a.EntryID = b.[ID]
WHERE a.BlogID = @blogID AND b.PostConfig = 93
ORDER BY a.WebCount DESC

Print | posted on Tuesday, March 22, 2005 8:45 PM

Feedback

# re: .Text Stats: Top 10 Web Views

left by Yama at 5/6/2005 10:31 AM
Hi,

Have you used paging in the past? Here is how you would do a paging query in T-SQL:

//-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
SELECT TOP <pageSize> *
FROM
(
SELECT TOP (<pageSize> * <pageIndex>)
< columns names>
FROM
< table name >
WHERE
< condition >
ORDER BY < PK column name > DESC
)
AS anyNameYouWant ORDER BY < PK column name > ASC
//-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~


<pageSize> = the number of records to be retrieved
<pageIndex> = the desired page index (1 based)
<column names> = list of the column names
<table name> = source table name and optional joined tables
<condition> = optional filter condition
< PK column name > = the primary key column name (it has to be also present into the <column names> list)



Sample T-SQL for getting 5 records from the Northwind Employee table from the second page:

//-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
USE Northwind
SELECT TOP 5 *
FROM(
SELECT TOP 10
EmployeeID,
LastName,
FirstName,
Title,
BirthDate,
HireDate
FROM
Employees

ORDER BY EmployeeID DESC

)
AS Employees ORDER BY EmployeeID ASC
//-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~


~yamazed

# re: .Text Stats: Top 10 Web Views

left by Yama at 5/6/2005 10:44 AM
USE Northwind

-- Page 1
SELECT TOP 3 *
FROM(
SELECT TOP 3
EmployeeID,
LastName,
FirstName,
Title,
BirthDate,
HireDate
FROM
Employees

ORDER BY EmployeeID DESC

)
AS Employees ORDER BY EmployeeID ASC

-- Page 2
SELECT TOP 3 *
FROM(
SELECT TOP 6
EmployeeID,
LastName,
FirstName,
Title,
BirthDate,
HireDate
FROM
Employees

ORDER BY EmployeeID DESC

)
AS Employees ORDER BY EmployeeID ASC

-- Page 3
SELECT TOP 3 *
FROM(
SELECT TOP 9
EmployeeID,
LastName,
FirstName,
Title,
BirthDate,
HireDate
FROM
Employees

ORDER BY EmployeeID DESC

)
AS Employees ORDER BY EmployeeID ASC

# re: .Text Stats: Top 10 Web Views

left by Dave at 5/6/2005 12:12 PM
Yama... thanks for the SQL help, but I don't need paging for any of these .Text statistics.

# re: .Text Stats: Top 10 Web Views

left by ringtones at 5/21/2005 8:57 AM
thanks
Comments have been closed on this topic.
Copyright © Dave Donaldson