Viewing Most Popular Links - Revisited
A little over a month ago I posted a SQL Query that can be used to find the most popular links
on a given portal. In that example if you had links that were being tracked that were files it would only
show the fileid for the file and not the file name. Now since there has been a fair amount of reader demand I have
created a new version of this query to actually show the URL for URL based links and to show the file name
for file based links.
To successfully parse the "fileid=xx" portion of the results when needed I use the substring function to
grab just the id so I can join to the files table to grab the file name. Due to the different methods needed
for files and links I have done this via two queries and then used the "UNION" statement to combine the results for
display. For you to use this on your portal you will need to be sure to modify the portalid value to match that
of your portal.
--Get standard links
SELECT m.ModuleTitle,
t.url,
t.clicks,
t.LastClick
FROM urltracking t
INNER JOIN Modules m
ON (t.ModuleId = m.ModuleId)
WHERE t.PortalId = 0
AND t.trackclicks = 1
AND t.urltype <> 'F'
--Combine with the others
UNION
--Get File links
SELECT m.ModuleTitle,
f.[FileName] as url,
t.clicks,
t.lastclick
FROM urltracking t
INNER JOIN Modules m
ON (t.moduleid = m.moduleid)
INNER JOIN Files f
--Grab the file id from the url field for the join
ON (CAST(SUBSTRING(t.url, 8, LEN(t.url)) as int) = f.fileid)
WHERE t.urltype = 'F'
ORDER BY t.clicks DESC, url
Please post any comments/questions below
Posted by IowaComputerGurus on Thursday, July 19, 2007
Name (required)
Email (required)
Website